generate a resultset for incrementing dates in TSQL and use last known value for quantity column

Question

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

Please note that your CTE will run VERY slowly for a large date range, it would be better to use something like [Itzik Ben-Gan's version](https://sqlblog.org/2009/10/07/bad-habits-to-kick-using-loops-to-populate-large-tables) to generate numbers and convert them to dates. — ErikE, Mar 13 '14 at 19:06
I took the answer and modified my implementation to generate numbers and convert to dates. — rguy, Mar 14 '14 at 13:21
It's funny, because now that I took the time to read the article I linked, it looks like Aaron says that the fastest method is the one you used in your original post! So please test out the different methods and see which one is fastest. I'm sorry if I misled you. — ErikE, Mar 14 '14 at 14:30

KekuSemau · Accepted Answer · 2014-03-14T10:13:26.667

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'

how can I use a Calendar-table to replace the recursion above? — rguy, Mar 13 '14 at 19:10

generate a resultset for incrementing dates in TSQL and use last known value for quantity column

1 Answers1

Linked