SQL如何编写返回缺失日期范围的查询? [英] SQL how to write a query that return missing date ranges?

问题描述

我想弄清楚如何编写查询来查看某些记录并找到今天和9999-12-31之间的缺失日期范围.我的数据如下所示:

I am trying to figure out how to write a query that looks at certain records and finds missing date ranges between today and 9999-12-31. My data looks like below:

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

所以对于这个数据，我希望我的查询返回:

So for this data I would want my query to return:

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

我不确定从哪里开始.这可能吗?

I'm not really sure where to start. Is this possible?

推荐答案

http://sqlfiddle.com/#!18/65238/1

SELECT
  *
FROM
(
  SELECT
    end_dt+1                            AS start_dt,
    LEAD(start_dt-1, 1, '9999-12-31')
      OVER (ORDER BY start_dt)
                                        AS end_dt
  FROM
    yourTable
)
  gaps
WHERE
  gaps.end_dt >= gaps.start_dt

但是，我强烈建议您使用排他性"的结束日期.也就是说，范围是直到但不包括 end_dt 的所有内容.

I would, however, strongly urge you to use end dates that are "exclusive". That is, the range is everything up to but excluding the end_dt.

这样，一天的范围就变成了 '2018-07-09', '2018-07-10'.

That way, a range of one day becomes '2018-07-09', '2018-07-10'.

很明显，我的范围是一天，如果你从另一个中减去一个，你就会得到一天.

It's really clear that my range is one day long, if you subtract one from the other you get a day.

此外，如果您更改为需要小时粒度或分钟粒度您无需更改数据.它只是有效.总是.可靠.直观.

Also, if you ever change to needing hour granularity or minute granularity you don't need to change your data. It just works. Always. Reliably. Intuitively.

如果您在网络上搜索，您会找到大量文档，说明为什么从软件的角度来看，inclusive-start 和 exclusive-end 是一个非常的好主意.(然后，在上面的查询中，您可以删除不稳定的 +1 和 -1.)

If you search the web you'll find plenty of documentation on why inclusive-start and exclusive-end is a very good idea from a software perspective. (Then, in the query above, you can remove the wonky +1 and -1.)

这篇关于SQL如何编写返回缺失日期范围的查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！