SQL 如何编写返回缺失日期范围的查询？

Question

我试图弄清楚如何编写一个查询来查看某些记录并查找今天和9999-12-31之间缺少的日期范围。我的数据如下所示:

ID      |start_dt                   |end_dt                     |prc_or_disc_1
10412   |2018-07-17 00:00:00.000    |2018-07-20 00:00:00.000    |1050.000000
10413   |2018-07-23 00:00:00.000    |2018-07-26 00:00:00.000    |1040.000000

因此，对于这些数据，我希望返回查询:

2018-07-10 | 2018-07-16
2018-07-21 | 2018-07-22
2018-07-27 | 9999-12-31

我不太确定从哪里开始。这可能吗？

Answer 1

您可以使用 MS SQL 中的 lag() 函数来做到这一点(但从 2012 年开始可用？)。

 with myData as
    (
      select *, 
      lag(end_dt,1) over (order by start_dt) as lagEnd
      from myTable),
    myMax as
    (
      select Max(end_dt) as maxDate from myTable
    )
    select dateadd(d,1,lagEnd) as StartDate, dateadd(d, -1, start_dt) as EndDate
    from myData
    where lagEnd is not null and dateadd(d,1,lagEnd) < start_dt
    union all
    select dateAdd(d,1,maxDate) as StartDate, cast('99991231' as Datetime) as EndDate 
    from myMax
    where maxDate < '99991231';

如果 lag() 在 MS SQL 2008 中不可用，那么您可以使用 row_number() 和连接来模拟它。