Results 1 to 2 of 2
  1. #1
    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:

    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!

  2. #2
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    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:
       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 foo
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts