Solved

Update SQL Server 2000 table with previous period data

Posted on 2010-11-30
12
414 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Database ERD 4 26
Powershell v3 - SQLCMD 3 26
Access join syntax when converting to T-SQL query 4 33
SQL Group By Question 4 18
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

839 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