检查 SQL Server 中每种特定类型的时间覆盖范围

Question

假设我在SQL中有以下数据表：

**TimeRange**
StartTime   -   EndTime   -   Type
----------------------------------
00:00       -   01:45     -   A
01:50       -   03:30     -   A
03:35       -   03:40     -   A
03:45       -   13:15     -   A
13:20       -   19:25     -   A
19:30       -   23:55     -   A
00:00       -   02:50     -   B
03:30       -   12:30     -   B
12:35       -   13:40     -   B
13:45       -   18:15     -   B
18:20       -   19:25     -   B
21:00       -   23:55     -   B
00:00       -   11:55     -   C
**Type**
TypeID  -   Name 
----------------
1       -   A 
2       -   B
3       -   C

类型表中的每种类型都应涵盖时间范围 00：00 - 23：55。每个开始时间 = 上一个结束时间 + 5。我们应该选择没有覆盖率的行。在上述示例中，应选择以下内容：

Output:
StartTime  EndTime Type
------------------------
02:55      03:25    B
19:30      20:55    B
12:00      23:55    C

你能帮我为此目的编写相应的查询吗？

提前感谢！

Answer 1

如果您使用的是 SQL Server 2012 或更高版本，则可以使用 Lag：

设置

CREATE TABLE #TimeRange
(
StartTime Time,
EndTime   Time,
[Type]     CHAR(1)
)
INSERT INTO #TimeRange
VALUES
('00:00','01:45','A'),
('01:50','03:30','A'),
('03:35','03:40','A'),
('03:45','13:15','A'),
('13:20','19:25','A'),
('19:30','23:55','A'),
('00:00','02:50','B'),
('03:30','12:30','B'),
('12:35','13:40','B'),
('13:45','18:15','B'),
('18:20','19:25','B'),
('21:00','23:55','B'),
('00:00','11:55','C')

编辑设置

CREATE TABLE #Type
(
TypeId INT,
Name    CHAR(1)
)
INSERT INTO #Type
Values (1, 'A'), (2, 'B'), (3,'C'), (4,'D')

查询

;WITH CTEPrevEndTime
AS
(
-- This finds the Previous End Time for each row for the same Type
SELECT [Type], 
StartTime, 
EndTime, 
COALESCE(
DATEADD(mi, 5, 
LAG(EndTime) OVER (Partition By [Type] ORDER BY StartTime )
), StartTime) As PrevEndTime
FROM #TimeRange
)
-- Find the records with gaps
SELECT [Type], PrevEndTime As StartTime, DATEADD(mi, -5, StartTime) As EndTime
FROM CTEPrevEndTime
WHERE PrevEndTime <> StartTime
UNION
-- find the [Type] that doesn't start at 00:00:00
SELECT [Type], CAST('00:00' AS Time) As StartTime, DateAdd(mi, -5, Min(StartTime)) As EndTime
FROM #TimeRange
GROUP BY [Type]
HAVING Min(StartTime) > '00:05'
UNION
-- find the [Type] that doesn't End at 23:55:00
SELECT [Type], DATEADD(mi, 5,Max(EndTime)) AS StartTime, CAST('23:55' AS TIME) As EndTime
FROM #TimeRange
GROUP BY [Type]
HAVING Max(EndTime) < '23:50'
UNION
--Edit
-- Find Types with no TimeRange
SELECT T1.Name As [Type], CAST('00:00' AS Time) As StartTime, CAST('23:55' AS TIME) As EndTime
FROM #Type T1
WHERE Not Exists (SELECT * FROM #TimeRange T2 WHERE  T1.Name = T2.Type)