Join values ​​with DateDim, where null'd dates will take the last non-null value in the table

advertisements

I need to do a join but I'm not sure which type. I have a table like this:

Date        Amount | FOO
------------------
2012-01-12       x
2012-03-14       y
2012-05-06       z
2012-05-14      aa
2012-09-02      bb

I am joining this with DateDim (Google here: DATE DIM, which is a table of dates (historical and future).

I need a query that would display data like this:

datedim.Date    foo.Amount | FOO x DATEDIM
------------------------------------------
2012-01-12               x
2012-01-13               x
2012-01-14               x
... etc...
2012-03-14               y
2012-03-15               y
2012-03-16               y
2012-03-17               y
... etc...
2012-05-06               z
... etc...

Basically, I need the values to persist (were it a left join, it would be NULLs) until the next non-null value. That will persist too... etc..


What I have so far...

SELECT datedim.Date
    ,CASE
        WHEN Amount IS NULL
        THEN (SELECT TOP 1 Amount
        FROM FOO WHERE foo.Date <= datedim.Date
        ORDER BY Date DESC)
        ELSE Amount END AS Amount
FROM DATEDIM datedim
LEFT JOIN FOO foo
ON foo.Date = datedim.Date

I need to create a view out of this. I'm getting an error saying ORDER BY is invalid for views, unless specified by TOP??? I do have a TOP in the subquery...


In SQLServer2005+ use recursive CTE

;WITH cte (id, [Date], Amount) AS
 (
  SELECT ROW_NUMBER() OVER (ORDER BY [Date] ASC) AS id,
         [Date], Amount
  FROM dbo.your_table t1
  ), cte2 (id, [Date], [LevelDate], Amount) AS
 (
  SELECT c1.id, c1.[Date], DATEDIFF(day, c1.[Date], c2.[Date]) AS [LevelDate], c1.Amount
  FROM cte c1 LEFT JOIN cte c2 ON c1.id = c2.id - 1
  ), cte3 (id, [Date], Amount, [Level]) AS
 (
  SELECT  id, [Date], Amount, 1 AS [Level]
  FROM cte2 c
  UNION ALL
  SELECT c.id, DATEADD(day, 1, ct.[Date]) AS [Date], c.Amount, ct.[Level] + 1
  FROM cte2 c JOIN cte3 ct ON c.id = ct.id
  WHERE c.[LevelDate] > ct.[Level]
  )
  SELECT [Date], Amount
  FROM cte3
  ORDER BY Date
  OPTION (maxrecursion 0)

Demo on SQLFiddle