Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Inserting dynamic rows, calendar table?

    Hi,
    I have a result set of data for tracking the price and price change of various items over the months. The existing result only includes an entry for an item if it changes in the month, so a sample result set is as follows:

    Code:
    itemID     amount       amountChange      theMonth (YYYY-MM-DD)
    1            200                200          2010-01-01    -- Item 1 created
    2            400                400          2010-02-01    -- Item 2 created
    1            500                300          2010-03-01    -- Item 1 price +300 
    2            500                100          2010-03-01    -- Item 2 price +100
    I would like to include an entry for each item in each month, after it was created, even if its price never changed, up to a specified end date. The change amount would obviously be zero, and the amount would be the previous amount for that item. Hence if the end date was 2010-05-01, I would want the following:

    Code:
    itemID     amount     amountChange     theMonth (YYYY-MM-DD)
    1           200          200            2010-01-01          -- Item 1 created
    1           200          0              2010-02-01          -- No change item 1
    2           400          400            2010-02-01          -- Item 2 created
    1           500          300            2010-03-01          -- Item 1 price +300 
    2           500          100            2010-03-01          -- Item 2 price +100 
    1           500          0              2010-04-01          -- No change item 1
    2           500          0              2010-04-01          -- No change item 2
    1           500          0              2010-05-01          -- No change item 1
    2           500          0              2010-05-01          -- No change item 2
    I feel I need to use a calendar table/temp table containing the months and do an outer join but when I do this I only get a single entry for months 2010-04-01 and 2010-05-01, as opposed to an entry for each item in those months.

    Can anyone assist?
    thanks
    Last edited by FLANDERS; 09-10-10 at 06:38.

  2. #2
    Join Date
    Nov 2009
    Posts
    37
    After much head-bashing I found how to do it, using multiple CTEs.

    Code:
    -- Generate a calendar table containing the months from start to end date
    WITH cte_months 
    AS 
    ( 
    	SELECT cast('2009-10-01' as datetime) AS aMonth     -- parameter
    	UNION ALL 
    	SELECT DATEADD(mm, 1, aMonth) 
    	FROM cte_months
    	WHERE DATEADD(mm, 1, aMonth) < '2010-10-01'        -- parameter
    ), 
    -- Get all item changes within the time for the specified case type (this is the old query basically)
    cte_item_changes AS (
    	select * 
    	from VitemChangesByMonth 
    	where '2009-10-01' <= itemDATE and               -- parameter
    	itemDATE < '2010-10-01'                          -- parameter
    ),
    -- Generate a items calendar. This generates a results set with 
    -- each item returned from cte_item_changes having 12 records (one for each month) 
    cte_calendar_items as (
    	select cr.itemId, cr.aMonth, rc.itemDate, rc.itemAmount, rc.itemAmountChange,
    	ROW_NUMBER() OVER (PARTITION BY cr.itemID ORDER BY cr.amonth) as rn  -- Allows joining of each row to valid row
    	from (
    		select distinct rc.itemID, m.amonth from cte_months m, cte_item_changes rc where 1 = 1
    	) cr left outer join cte_item_changes rc on cr.itemID = rc.itemid and cr.amonth = rc.itemmonth 
    ) 
    
    -- Fills in the blanks of the items table by joining each item change record
    -- to the previous valid record for the same item, to allow calculating of amount and change
    select 
    a.itemID,    
    isnull(a.itemAmount, b.itemAmount) as itemAmount,
    isnull(a.itemAmountChange, 0) as itemAmountChange,	   
    a.aMonth, 
    isnull(a.itemDate, a.aMonth) as itemDate
    FROM cte_calendar_items a, cte_calendar_items b
    where a.itemID = b.itemID AND b.rn = (
        -- Get the latest "real" record that relates to same item. Latest is determined by the
        -- row number column, while a real record is denoted by not having a NULL item date. 
        select max(c.rn) from cte_calendar_items c where c.itemId = a.itemId and c.rn <= a.rn and c.itemdate is not null
    )
    I used the original view (VitemChangesByMonth) data and filled in the missing months using the calendar table. This couldnt be done in the view as the calendar is an arbitary year, as selected by the user. It seems to work as required

Posting Permissions

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