Solved

Update SQL Server 2000 table with previous period data

Posted on 2010-11-30
12
411 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:printmedia
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks to the both of you! I will ask my "curiosity question" in another post.
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
Please see your other q on this topic, I've posted the answer there :-) .
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now