04-05-10, 11:23 #1Registered User
- Join Date
- Apr 2010
Unanswered: Update current period data based on previous period data - using stored procedure?
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 |
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.
04-05-10, 13:59 #2Resident Curmudgeon
Provided Answers: 54
- Join Date
- Feb 2004
- In front of the computer
Don't update anything, knowing which months have data and which don't will become important at some point in time (it always does). Just use something like:Code:
SELECT [PRODUCT ID], [ENTITY ID], [MONTH], (SELECT bar.[COST] FROM [yingchai] AS bar WHERE bar.[PRODUCT ID] = foo.[PRODUCT ID] AND bar.[MONTH] = (SELECT Max(bat.[MONTH]) FROM [yingchai] AS bat WHERE bat.[PRODUCT ID] = foo.[PRODUCT ID] AND bat.[MONTH] <= foo.[MONTH] AND bat.[COST] IS NOT NULL)) FROM [yingchai] AS fooIn theory, theory and practice are identical. In practice, theory and practice are unrelated.