生成用于在 TSQL 中递增日期的结果集并使用最后一个已知值作为数量列 [英] generate a resultset for incrementing dates in TSQL and use last known value for quantity column

问题描述

使用开始日期和结束日期，我们需要生成一个天数列表以及每天的手头数量余额.当一天的手头数量记录不存在时，结果集应使用手头数量的最新已知值.

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.

手头上.

例如，使用此数据作为我的 qoh 表

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

我想要选择的结果是

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

我已经试过了

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

返回这些结果.我想用最后一个已知值替换 NULLS.

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

推荐答案

只需稍作调整即可工作:

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

返回

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.

设置日历表:

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

减少查询:

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'

这篇关于生成用于在 TSQL 中递增日期的结果集并使用最后一个已知值作为数量列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！