Update current period data based on previous perio

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update current period data based on previous perio

Author

Topic

yingchai
Starting Member

33 Posts

Posted - 2010-04-05 : 11:38:10

Hi to all gurus,

I have a problem here and below is my story:
My client had prepared their yearly cost data and I had loaded the data into my fact table. Bear in mind that some of the month might not have cost incurred. When I pull the cost data in my SQL Server Management Studio, here is roughly how it looks like:


PRODUCT ID   | ENTITY ID    | MONTH   | COST 
------------------------------------------------- 
CMP1000      | PM1          | Jan.09  | 100 
CMP1000      | PM1          | Feb.09  | 80 
CMP1000      | PM1          | Mar.09  | 
CMP1000      | PM1          | Apr.09  | 110 
CMP1000      | PM1          | May.09  | 
CMP1000      | PM1          | Jun.09  | 
CMP1000      | PM1          | Jul.09  |

Now the problem is how can we automatically update the COST column so that if there is no cost incurred in Mar.09, it'll automatically refer to last month's cost? The same goes to the cost for May.09, Jun.09 and Jul.09. It should take the 110 figure for this period.

The reason for this is to calculate the product profitability. We'll take the SALES figure minus the COST figure. Eg; if there is no cost incurred in Jul.09, the product profitability will be calculated as SALES in Jul.09 minus COST in Jul.09 which is 110.

I'm thinking of writing a stored procedure and append the NULL costs with updated values into another column. But I'm not sure how to do it.

If fellow members here came across this situation before or get to know a more feasible solution, pls share with me.

Many thanks!

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-05 : 11:43:59

This may work.


UPDATE t SET
   COST = latestCost.cost
FROM
    <theTable> t
    OUTER APPLY (
        SELECT TOP 1 cost AS cost
        FROM <theTable> t2
        WHERE t2.[PRODUCT ID] = t.[PRODUCT ID]
              AND t2.[ENTITY ID] = t.[ENTITY ID]
              AND t2.MONTH < t.MONTH
              AND t2.COST IS NOT NULL
        ORDER BY
              t2.MONTH DESC
        )
        latestCost
WHERE
    t.COST IS NULL

If you want to check just run it in SELECT mode


SELECT
    t.*
    , latestCost.cost AS [Going to UPDATE To]
FROM
    <theTable> t
    OUTER APPLY (
        SELECT TOP 1 cost AS cost
        FROM <theTable> t2
        WHERE t2.[PRODUCT ID] = t.[PRODUCT ID]
              AND t2.[ENTITY ID] = t.[ENTITY ID]
              AND t2.MONTH < t.MONTH
              AND t2.COST IS NOT NULL
        ORDER BY
              t2.MONTH DESC
        )
        latestCost
WHERE
    t.COST IS NULL

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-05 : 11:45:18

I'm assuming here that your MONTH column is actually a DATETIME field?

Or is it a character field?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 11:45:25

[code]SELECT t.[PRODUCT ID] , t.[ENTITY ID],t.[MONTH],COALESCE(t.COST,t1.COST)
FROM Table t
OUTER APPLY (SELECT TOP 1 Cost
FROM Table
WHERE [PRODUCT ID] = t.[PRODUCT ID]
AND [ENTITY ID] = t.[ENTITY ID]
AND CAST('01 '+ REPLACE([MONTH],'.',' ') < CAST('01 '+ REPLACE(t.[MONTH],'.',' ')
AND COST IS NOT NULL
ORDER BY CAST('01 '+ REPLACE([MONTH],'.',' ') DESC)t1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-05 : 11:47:29

Hi Visakh -- pretty similar!

Except -- you need to include a check for NULL inside your OUTER APPLY and I need to match on entity ID as well!

Between us it will be right!

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources