Solved

Update SQL Server 2000 table with previous period data

Posted on 2010-11-30
12
413 Views
Last Modified: 2012-05-10
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!
0
Comment
Question by:printmedia
  • 5
  • 4
  • 3
12 Comments
 
LVL 39

Expert Comment

by:lcohan
ID: 34239893
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
 

Author Comment

by:printmedia
ID: 34239974
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
 
LVL 39

Accepted Solution

by:
lcohan earned 250 total points
ID: 34240253
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 250 total points
ID: 34240279
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
 

Author Comment

by:printmedia
ID: 34240485
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
 
LVL 39

Expert Comment

by:lcohan
ID: 34240524
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
 

Author Comment

by:printmedia
ID: 34240617
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
 
LVL 39

Expert Comment

by:lcohan
ID: 34240849
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
 

Author Closing Comment

by:printmedia
ID: 34241010
Thanks to the both of you! I will ask my "curiosity question" in another post.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34241438
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
 

Author Comment

by:printmedia
ID: 34241473
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
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 34241759
Please see your other q on this topic, I've posted the answer there :-) .
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question