检查两个日期之间的间隔

Question

我有一个时间间隔从日期a到日期b的表。间隔以30分钟为单位，如下所示:

2021-02-22 00:00:00.000
2021-02-22 00:30:00.000
2021-02-22 01:00:00.000
2021-02-22 01:30:00.000
2021-02-22 02:00:00.000
2021-02-22 02:30:00.000
2021-02-22 03:00:00.000
[...]
2021-03-21 23:30:00.000

我必须每隔30分钟检查一下我的MIN(date) = 2021-02-22 00:00:00.000到MAX(date) = 2021-03-21 23:30:00.000是否有记录。

每个日历周的COUNT()为336。每天30分钟*一周7天= (24 * 2)* 7).
我试图检查每个日历周是否有336条记录，但如果一条记录缺失，一条记录存在两次，计数为336，但结果是错误的。

SELECT *
FROM (
SELECT a,
b,
DATEPART(iso_week, interval_datetime) AS cw,
COUNT(id)                             AS counts
FROM data_table
GROUP BY DATEPART(iso_week, interval_datetime),
a,
b
) AS xyz
WHERE counts <> 336;

是否有其他方法检查是否有重复记录和检查是否每30分钟使用一次记录?也许不用"日期"表

谢谢!

Answer 1

WITH CTE(CHECK_TIME) AS
(
SELECT '2021-02-22 00:00:00.000'
UNION ALL
SELECT'2021-02-22 00:30:00.000'
UNION ALL
SELECT'2021-02-22 01:00:00.000'
UNION ALL
SELECT'2021-02-22 01:30:00.000'
UNION ALL
SELECT'2021-02-22 02:00:00.000'
UNION ALL
SELECT'2021-02-22 02:30:00.000'
UNION ALL
SELECT'2021-02-22 03:00:00.000'
UNION ALL
SELECT '2021-02-22 03:18:00.000'--FAIL ROW
UNION ALL
SELECT'2021-02-22 03:00:00.000'--DOUBLED RECORD
)
SELECT C.CHECK_TIME,LAG(CHECK_TIME)OVER(ORDER BY C.CHECK_TIME)LAGG,
ABS(DATEDIFF(MI,C.CHECK_TIME,LAG(CHECK_TIME)OVER(ORDER BY C.CHECK_TIME)))AS         MINUTES_DIFFERENCE
FROM CTE AS C

请检查这种方法是否有助于隔离有问题的记录。我把它作为答案贴出来，因为它太长了，不能评论

Answer 2

您可以使用LEAD来获取下一个值，然后进行筛选，以便它返回间隙太大的所有值

SELECT *
FROM (
SELECT a,
b,
interval_datetime,
LEAD(interval_datetime) OVER (ORDER BY interval_datetime) AS nextInterval
FROM data_table
) AS t
WHERE nextInterval IS NULL OR DATEDIFF(second, interval_datetime, nextInterval) > 1800;