我在做基于时间员工工作的阻止某些报告。 在某些情况下，该数据包含了什么是真正的时间单块两个独立的记录。
这里的桌子和一些样本记录的基本版本：
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分以上相邻的记录，但不是必需的。
本文提供了你的问题相当多的可能的解决方案
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;
如果这是严格有关相邻行（不重叠的），你可以试试下面的方法：
unpivot的时间戳。
只留下那些没有重复。
转动其余的背面，每个连接Start与直接下End 。
或者，在Transact-SQL中，这样的事情：
WITH unpivoted AS (
SELECT
EmpID,
event,
dtime,
count = COUNT(*) OVER (PARTITION BY EmpID, dtime)
FROM atable
UNPIVOT (
dtime FOR event IN (StartTime, EndTime)
) u
)
, filtered AS (
SELECT
EmpID,
event,
dtime,
rowno = ROW_NUMBER() OVER (PARTITION BY EmpID, event ORDER BY dtime)
FROM unpivoted
WHERE count = 1
)
, pivoted AS (
SELECT
EmpID,
StartTime,
EndTime
FROM filtered
PIVOT (
MAX(dtime) FOR event IN (StartTime, EndTime)
) p
)
SELECT *
FROM pivoted
;
有此查询演示在SQL小提琴 。
我已经改变了莉儿位的名称和类型，以使示例小，但这个作品，应该是非常快的，它有没有数量的限制的记录：
with cte as (
select
x1.id
,x1.t1
,x1.t2
,case when x2.t1 is null then 1 else 0 end as bef
,case when x3.t1 is null then 1 else 0 end as aft
from x x1
left join x x2 on x1.id=x2.id and x1.t1=x2.t2
left join x x3 on x1.id=x3.id and x1.t2=x3.t1
where x2.id is null
or x3.id is null
)
select
cteo.id
,cteo.t1
,isnull(z.t2,cteo.t2) as t2
from cte cteo
outer apply (select top 1 *
from cte ctei
where cteo.id=ctei.id and cteo.aft=0 and ctei.t1>cteo.t1
order by t1) z
where cteo.bef=1
和捣鼓它： http://sqlfiddle.com/#!3/ad737/12/0
期权与内联用户定义函数和CTE
CREATE FUNCTION dbo.Overlap
(
@availStart datetime,
@availEnd datetime,
@availStart2 datetime,
@availEnd2 datetime
)
RETURNS TABLE
RETURN
SELECT CASE WHEN @availStart > @availEnd2 OR @availEnd < @availStart2
THEN @availStart ELSE
CASE WHEN @availStart > @availStart2 THEN @availStart2 ELSE @availStart END
END AS availStart,
CASE WHEN @availStart > @availEnd2 OR @availEnd < @availStart2
THEN @availEnd ELSE
CASE WHEN @availEnd > @availEnd2 THEN @availEnd ELSE @availEnd2 END
END AS availEnd
;WITH cte AS
(
SELECT EmpID, Start, [End], ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Start) AS Id
FROM dbo.TableName
), cte2 AS
(
SELECT Id, EmpID, Start, [End]
FROM cte
WHERE Id = 1
UNION ALL
SELECT c.Id, c.EmpID, o.availStart, o.availEnd
FROM cte c JOIN cte2 ct ON c.Id = ct.Id + 1
CROSS APPLY dbo.Overlap(c.Start, c.[End], ct.Start, ct.[End]) AS o
)
SELECT EmpID, Start, MAX([End])
FROM cte2
GROUP BY EmpID, Start
演示上SQLFiddle
CTE与累计总和：
DECLARE @t TABLE(EmpId INT, Start TIME, Finish TIME)
INSERT INTO @t (EmpId, Start, Finish)
VALUES
(1001, '10:00 AM', '12:00 PM'),
(1001, '4:00 PM', '5:30 PM'),
(1001, '5:30 PM', '8:00 PM')
;WITH rowind AS (
SELECT EmpId, Start, Finish,
-- IIF returns 1 for each row that should generate a new row in the final result
IIF(Start = LAG(Finish, 1) OVER(PARTITION BY EmpId ORDER BY Start), 0, 1) newrow
FROM @t),
groups AS (
SELECT EmpId, Start, Finish,
-- Cumulative sum
SUM(newrow) OVER(PARTITION BY EmpId ORDER BY Start) csum
FROM rowind)
SELECT
EmpId,
MIN(Start) Start,
MAX(Finish) Finish
FROM groups
GROUP BY EmpId, csum
