我正在做一些基于员工工作时间的报告.在某些情况下,数据包含两个单独的记录,用于确切的单个时间块.
这是该表的基本版本和一些示例记录：
EmployeeID StartTime EndTime
数据：
EmpID Start End ---------------------------- #1001 10:00 AM 12:00 PM #1001 4:00 PM 5:30 PM #1001 5:30 PM 8:00 PM
在该示例中,最后两个记录在时间上是连续的.我想写一个组合任何相邻记录的查询,所以结果集是这样的：
EmpID Start End ---------------------------- #1001 10:00 AM 12:00 PM #1001 4:00 PM 8:00 PM
理想情况下,它还应该能够处理2个以上的相邻记录,但这不是必需的.
解决方法
本文为您的问题提供了一些可能的解决方案
这个似乎是最直接的：
WITH StartTimes AS
(
SELECT DISTINCT username,starttime
FROM dbo.Sessions AS S1
WHERE NOT EXISTS
(SELECT * FROM dbo.Sessions AS S2
WHERE S2.username = S1.username
AND S2.starttime < S1.starttime
AND S2.endtime >= S1.starttime)
),EndTimes AS
(
SELECT DISTINCT username,endtime
FROM dbo.Sessions AS S1
WHERE NOT EXISTS
(SELECT * FROM dbo.Sessions AS S2
WHERE S2.username = S1.username
AND S2.endtime > S1.endtime
AND S2.starttime <= S1.endtime)
)
SELECT username,starttime,(SELECT MIN(endtime) FROM EndTimes AS E
WHERE E.username = S.username
AND endtime >= starttime) AS endtime
FROM StartTimes AS S;
