Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update SQL Server 2000 table with previous period data

Posted on 2010-11-30
12
Medium Priority
?
427 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 40

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 40

Accepted Solution

by:
lcohan earned 1000 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
What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 1000 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 40

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 40

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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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