查找/创建一系列跟踪记录的正确结束日期

Question

这一个很糟糕，所以跟我来。

我有两张桌子，可以追踪不同地点的人。我已经使用LEAD和LAG成功地将它们合并在一起，从而在单个表中创建无缝转换
我现在的问题是，对于其中一张表，我需要包括一些额外的活动项目，这些项目位于某些部分中
为了简单起见，我有以下正常情况：

| System |  ID | Item |         Start         |         End
|  Alpha | 987 |  123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 
|  Alpha | 374 |  123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 
|  Beta  | 184 |  123 | May, 20 2015 10:00:00 | May, 20 2015 11:00:00 
|  Beta  | 798 |  123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 

现在，这些额外的项目位于某些记录中，所以我的数据看起来像这样：

| System   |  ID | Item |         Start         |         End
|  Alpha   | 987 |  123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 
|  Alpha   | 374 |  123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 
|  Beta    | 184 |  123 | May, 20 2015 10:00:00 | May, 20 2015 11:00:00 
|  Charlie | 874 |  123 | May, 20 2015 10:20:00 | May, 20 2015 10:25:00  
|  Charlie | 984 |  123 | May, 20 2015 10:37:00 | May, 20 2015 10:54:00  
|  Beta    | 798 |  123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 

请注意，这两个Charlie事件发生在Beta 184中。

目前，我已经将表格分解为每分钟的记录（下一步需要，不要问），并在表格中显示正确的记录，但我不知道如何将其重新组合起来，并在每个片段上有正确的开始和结束时间。此外，不知何故，新的记录将被形成以填补空白。

我希望上述情况的最终结果是：

| System   |  ID | Item |         Start         |         End
|  Alpha   | 987 |  123 | May, 20 2015 07:00:00 | May, 20 2015 08:00:00 |
|  Alpha   | 374 |  123 | May, 20 2015 08:00:00 | May, 20 2015 10:00:00 |
|  Beta    | 184 |  123 | May, 20 2015 10:00:00 | May, 20 2015 10:20:00 |
|  Charlie | 874 |  123 | May, 20 2015 10:20:00 | May, 20 2015 10:25:00 |
|  Beta    | 184 |  123 | May, 20 2015 10:25:00 | May, 20 2015 10:37:00 | new
|  Charlie | 984 |  123 | May, 20 2015 10:37:00 | May, 20 2015 10:54:00 | 
|  Beta    | 184 |  123 | May, 20 2015 10:54:00 | May, 20 2015 11:00:00 | new
|  Beta    | 798 |  123 | May, 20 2015 11:00:00 | May, 20 2015 12:00:00 |

这有道理吗？

希望有人能帮我。

Answer 1

您可以使用这样的东西：

DECLARE @Source TABLE (
    [System] VARCHAR(50),
    ID INT PRIMARY KEY,
    Item INT NOT NULL,
    Start DATETIME NOT NULL,
    [End] DATETIME NOT NULL,
    CHECK (Start<[End])
)
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Alpha',   987, 123, '2015-05-20 07:00', '2015-05-20 08:00')
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Alpha',   374, 123, '2015-05-20 08:00', '2015-05-20 10:00')
--INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES   ('Delta',   555, 123, '2015-05-20 09:30', '2015-05-20 10:00')
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Beta',    184, 123, '2015-05-20 10:00', '2015-05-20 11:00')
--INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES   ('Charlie', 111, 123, '2015-05-20 10:05', '2015-05-20 10:07')
--INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES   ('Charlie', 222, 123, '2015-05-20 10:10', '2015-05-20 10:20')
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 874, 123, '2015-05-20 10:20', '2015-05-20 10:25')
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Charlie', 984, 123, '2015-05-20 10:37', '2015-05-20 10:54')
INSERT INTO @Source (System, ID, Item, Start, [End]) VALUES ('Beta',    798, 123, '2015-05-20 11:00', '2015-05-20 12:00')
;WITH CTE AS (
    SELECT * 
    FROM @Source s1
    OUTER APPLY (
        SELECT MIN(s2.Start) AS NextStart
        FROM @Source s2
        WHERE s2.Start>s1.Start AND s2.Start<s1.[End]
    ) q2
    OUTER APPLY (
        SELECT MAX(s3.[End]) AS PreviousEnd
        FROM @Source s3
        WHERE s3.[End]>s1.Start AND s3.[End]<s1.[End]
    ) q3
)
SELECT System, ID, Item, Start, [End]
FROM CTE WHERE NextStart IS NULL AND PreviousEnd IS NULL
UNION ALL
SELECT System, ID, Item, Start, NextStart
FROM CTE WHERE NextStart IS NOT NULL
UNION ALL
SELECT System, ID, Item, PreviousEnd, [End]
FROM CTE WHERE PreviousEnd IS NOT NULL
UNION ALL
SELECT s4.System, s4.ID, s4.Item, q5.[End], q6.Start
FROM @Source s4
CROSS APPLY (
    SELECT *
    FROM @Source s5
    WHERE s5.Start>s4.Start AND s5.Start<s4.[End]
) q5
CROSS APPLY (
    SELECT TOP 1 *
    FROM @Source s6
    WHERE s6.Start>q5.Start AND s6.Start<s4.[End]
    ORDER BY s6.Start
) q6
WHERE q5.[End]<q6.Start
ORDER BY [Start]

UNION的第一部分处理不与任何其他间隔重叠的间隔。

第二部分处理在间隔结束时重叠的行。

第三部分处理在间隔开始时重叠的行。

最后一部分产生与基准区间重叠的另外两个区间之间的间隙（当两个区间不相邻时）。

Answer 2

看起来@RazvanSocol打败了我，但既然我做了这个，而且看起来比他的更简单，我也会把它发布在这里：

create table #times (
  Item int,
  EndTime datetime,
  primary key (Item, EndTime)
)
insert into #times 
select distinct Item, StartTime from timetable 
union 
select distinct Item, EndTime from timetable
;with CTE as (
  select
    System, ID, Item, StartTime
  from
    timetable T1
union all
  select
    T1.System, T1.ID, T1.Item, T2.EndTime
  from
    timetable T1
    join timetable T2 on T1.Item = T2.Item and 
        T1.StartTime < T2.StartTime and T1.EndTime > T2.EndTime
  where
    -- This check added to handle cases with adjacent ranges in the dates
    -- as pointed out by Razvan Socol
    not exists (select 1 from timetable T3 where T3.StartTime = T2.EndTime)
)
select
  System, ID, Item, StartTime, E.EndTime
from 
  CTE
  outer apply (
    select top 1 EndTime from #times T
    where T.Item = CTE.Item and T.EndTime > CTE.StartTime 
    order by EndTime asc
  ) E
order by Item, StartTime

我使用一个临时表来收集每个项目的所有不同开始/结束时间，然后在CTE中使用第二次选择来创建缺失的行，最后外部应用程序通过搜索该项目的最早日期来重新计算每一行的结束日期。

SQL Fiddle

编辑：添加了对相邻范围的检查

Answer 3

试试这个

Sql小提琴

;with cte as
(select distinct t1.system,t1.ID,t1.item,t2.start,
ROW_NUMBER()  over(order by t2.start) rownum 
from timetable t1 right join 
(select system,start from timetable 
union all
select system,[end] from timetable )
as t2 on t1.start = t2.start  
)
select 
case when c1.system is null
then
'Beta'
else
c1.system
end as system,
case when c1.id is null
then
'184'
else
c1.id
end as id,
case when c1.item is null
then
'123'
else
c1.item
end as item,c1.start,c2.start as [End] from cte c1 
join cte c2 on c1.rownum = c2.rownum-1 and c1.start != c2.start
order by c1.rownum;