问题描述
我想弄清楚如何编写查询来查看某些记录并找到今天和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如何编写返回缺失日期范围的查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!