Update SQL Server 2000 table with previous period data

Hi all.

I have a SQL Server 2000 table (TableA) with the following fields: ItemNumber, Year, CostPeriod1, CostPeriod2...CostPeriod12

Some months will have no data, for those months I would like to look at the previous period and insert whatever data they had in the previous period into the current one. If the previous period does not have any data then go with the value in a specific table (TableB), TableB has the itemnumber and the default cost to be used when all else fails.

Let's say I'm in the field CostPeriod1 of 2010 and there is no data, how do I go back to CostPeriod12 of 2009? Or I'm in CostPeriod3 of 2010 and I want to get the data from CostPeriod2 of 2010.

How can I do this? I am using a DTS to populate the data.

Thank you in advance!
printmediaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
lcohanConnect With a Mentor Database AnalystCommented:
Considering that you go back to the previous year for the CosPeriod1 only that particular case stmnt should look like below:

select ItemNumber, [Year],
            case when CostPeriod1 is null then (select CostPeriod12 from my_table b with (nolock) where b.[Year] = a.[Year] -1)
                  else CostPeriod1 end as CostPeriod1,
            CostPeriod2,
            CostPeriod3,
            CostPeriod4,
            CostPeriod5,
            CostPeriod6,
            CostPeriod7,
            CostPeriod8,
            CostPeriod9,
            CostPeriod10,
            CostPeriod11,
            CostPeriod12
from my_table a with (nolock)



0
 
lcohanDatabase AnalystCommented:
You should include a SQL step in your DTS to run some sql UPDATE FROM statement and use ISNULL or  build a CASE on the column(s) where you need to get/calculate a data value
0
 
printmediaAuthor Commented:
Thanks. But how do I deal with a situation when I have CostPeriod1 how do I tell it in the CASE to go to CostPeriod12 of the previous year?
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Scott PletcherConnect With a Mentor Senior DBACommented:
Use COALESCE; more specifically, like code below.

For best performance, your table should be *clustered* on (ItemNumber, Year) [presumbaly it already is :-) ].

SELECT
    COAELSCE(CostPeriod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod1,
    COALESCE(CostPeriod2, CostPeridod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod2,
    COALESCE(CostPeriod3, CostPeriod2, CostPeridod1, 
        (SELECT COALESCE(CostPeriod12, CostPeriod11, CostPeriod10, ..., CostPeriod1) 
         FROM TableA aPrevious
         WHERE aPrevious.ItemNumber = a.ItemNumber AND aPrevious.Year = a.Year - 1
        ) ) AS CostPeriod3,
    ...
FROM TableA a

Open in new window

0
 
printmediaAuthor Commented:
Thanks Scott. I will give your code a try and let you know how it worked. Out of curiosity, what if I didn't want to look back to the previous year, but as far back as I had data. So instead of looking at 2009, I wanted to look back 2008, 2007, 2006 etc. Would I simply do: AND aPrevious.Year <= a.Year or will that give me an error?
0
 
lcohanDatabase AnalystCommented:
Depending what you want to achieve:

ISNULL looks at the first parameter. The second parameter is returned in place of the first parameter only if the first parameter is null.

COALESCE looks at all parameters, left to right, and returns the first parameter that is not null (if any).

In my example I did not used ISNULL or COALESCE as they may invalidate an index if exists but did only what was asked for in a CASE stmnt wich will use an index if exists.
0
 
printmediaAuthor Commented:
Thanks lcohan. But what if I don't want to look at just the previous period (I know that's what I originally asked) but rather pick up the data from the last period to have data. So if I'm on period11 of 2010 then keep going back until say period 11 of 2009 which is the latest one to have data. How can I do that with a CASE statement which you suggested.
0
 
lcohanDatabase AnalystCommented:
Then you have no choice indeed but to use the COALESCE that looks at at all parameters, left to right, and returns the first parameter that is not null (if any).

0
 
printmediaAuthor Commented:
Thanks to the both of you! I will ask my "curiosity question" in another post.
0
 
Scott PletcherSenior DBACommented:
Hmm, to me the original q was never just go back one month for one particular case, since the original q stated (emphasis added):
"
Some month*s* will have no data, for those month*s* I would like to look at the previous period and insert whatever data they had in the previous period into the current one.
"

Presumably if one month/period could be missing data the previous one could also, right?



>>  Out of curiosity, what if I didn't want to look back to the previous year, but as far back as I had data. <<

You can extend the COALESCE.  For performance reasons, you could include a join to the previous year(s) in your main query, then use the relevant columns in the COALESCE.
0
 
printmediaAuthor Commented:
But would I have to know how far back the item has data? For example, Item1 may have data from 2008, 2009 and 2010, while Item2 only has data for 2009 and 2010 and Item3 only has data for 2007.

How can I extend the COALESCE if the years vary from item to item?
0
 
Scott PletcherSenior DBACommented:
Please see your other q on this topic, I've posted the answer there :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.