generate a resultset for incrementing dates in TSQ

问题:

Using start and end dates, we need to generate a list of days in between with the quantity on hand balance for each day. When the quantity on hand record for a day does not exist, the result set should use the most recent known value for the quantity on hand.

on hand.

for example, using this data as my qoh table

create table #t1
(postdate date,
qoh int)

insert #t1 select '1/1/2014', 10
insert #t1 select '1/5/2014', 30
insert #t1 select '1/9/2014', 50
insert #t1 select '1/11/2014', 60

my desired results to select are

2014-01-01  10
2014-01-02  10
2014-01-03  10
2014-01-04  10
2014-01-05  30
2014-01-06  30
2014-01-07  30
2014-01-08  30
2014-01-09  50
2014-01-10  50
2014-01-11  60

I've tried this

WITH dates AS
(
    SELECT CAST('1/1/2014' AS DATE) 'date'
    UNION ALL
    SELECT DATEADD(day, 1, t.date)
    FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= '3/1/2014'
)

SELECT dates.date,
    (SELECT TOP 1 qoh FROM #t1
     WHERE #t1.postdate = dates.date
     ORDER BY date ASC)
FROM dates

which returns these results. I want to replace the NULLS with with last known values.

date    (No column name)
2014-01-01  10
2014-01-02  NULL
2014-01-03  NULL
2014-01-04  NULL
2014-01-05  30
2014-01-06  NULL
2014-01-07  NULL
2014-01-08  NULL
2014-01-09  50
2014-01-10  NULL
2014-01-11  60

回答1:

It works with just minor adjustments:

WITH dates AS
(
    SELECT CAST('20140101' AS DATE) 'date'
 UNION ALL
    SELECT DATEADD(day, 1, D.date)
    FROM dates D
    WHERE DATEADD(dd, 1, D.date) <= '20140301'
)
SELECT 
    D.date
    ,(  SELECT TOP 1 qoh
        FROM #t1 T
        WHERE T.postdate <= D.[date]
        ORDER BY T.postdate DESC
    )
FROM 
    dates D

This returns

2014-01-01  10
2014-01-02  10
2014-01-03  10
2014-01-04  10
2014-01-05  30
2014-01-06  30
2014-01-07  30
2014-01-08  30
2014-01-09  50
2014-01-10  50
2014-01-11  60
2014-01-12  60
2014-01-13  60
... ...

http://sqlfiddle.com/#!6/79578/1

Just a sidenote: I prefer to use a Calendar-table (if possible). Just throw in each day for the next ten years and it's still a very small table and you can join on it to return one row per day. It's quite convenient and the queries are easier to read than such recursion.

EDIT:

Set up a calendar table:

CREATE table tblCalendar ([date] date PRIMARY KEY);

DECLARE @n int;
SET @n = 0;

WHILE @n < (365*5)
BEGIN
  INSERT INTO tblCalendar([date]) VALUES(DATEADD(day, @n, '20140101'));
  SET @n = @n +1;
END

Cut down the query to:

SELECT 
    C.[date]
    ,(  SELECT TOP 1 qoh
        FROM @t1 T1
        WHERE T1.postdate <= C.[date]
        ORDER BY T1.postdate DESC
    )
FROM 
    tblCalendar C
WHERE
    C.date BETWEEN '20140101' AND '20140301'