我根据员工的工作时间做一些报告。在某些情况下,数据包含两个单独的记录,这实际上是一个时间段。
这是该表的基本版本和一些示例记录:
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
理想情况下,它还应该能够处理两个以上的相邻记录,但这不是必需的。
本文为您的问题提供了许多可能的解决方案
http://www.sqlmag.com/blog/puzzled-by-t-sql-blog-15/tsql/solutions-to- packing-date-and-time-intervals- puzzle-136851
这似乎是最直接的方法:
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;