T-SQL起始日期和结束日期时间在单个列中

Question

我有一张关于不同资产的开/关事件的表格。我需要在不使用游标的情况下获取开始和停止事件时间的列表。

来源：

Item      EventDate               Event
A         2011-10-03 00:01:00     On
B         2011-10-03 00:01:00     On
A         2011-10-03 00:02:00     Off
C         2011-10-03 00:01:00     On
B         2011-10-03 00:02:00     Off
A         2011-10-03 00:02:02     On      
C         2011-10-03 00:02:05     On
A         2011-10-03 00:02:07     Off

所需结果：

Item      Start                   End
A         2011-10-03 00:01:00     2011-10-03 00:02:00
A         2011-10-03 00:02:02     2011-10-03 00:02:07
B         2011-10-03 00:01:00     2011-10-03 00:01:00
C         2011-10-03 00:01:00     2011-10-03 00:02:05

Answer 1

这是解决方案，带有概念验证供所有人验证。

我注意到的Off事件C被标记为新事件On。我解决了这个问题，但是这也导致我编写了一个解决方案，该解决方案允许发生一个已经开始但尚未结束的事件，因此我加入了一个开放式事件D。

另外，我的解决方案在重叠的时期内有效。

declare @YourTable table (Item varchar(10), 
                          EventDate datetime, 
                          Event varchar(10))

insert into @YourTable values
('A',         '2011-10-03 00:01:00',     'On'),
('B',         '2011-10-03 00:01:00',     'On'),
('A',         '2011-10-03 00:02:00',     'Off'),
('C',         '2011-10-03 00:01:00',     'On'),
('B',         '2011-10-03 00:02:00',     'Off'),
('A',         '2011-10-03 00:02:02',     'On'),  
('C',         '2011-10-03 00:02:05',     'Off'),
('A',         '2011-10-03 00:02:07',     'Off'),
('D',         '2011-10-03 00:02:02',     'On')

select tOn.Item, tOn.EventDate Start, tOff.EventDate [End]
from (
select Item, EventDate, 
       ROW_NUMBER() Over(Partition by Item order by EventDate) EventID
from @YourTable where Event = 'On'
) tOn
LEFT JOIN (
select Item, EventDate, 
       ROW_NUMBER() Over(Partition by Item order by EventDate) EventID
from @YourTable where Event = 'Off'
) tOff
on (tOn.Item = tOff.Item and tOn.EventID = tOff.EventID)

讲解

我们将数据集划分为2：On事件和Off事件。每个包含编号的行，Item更改后将重新启动。

基本上，我们采用先进先出的方式：第一个On将被first封闭Off，因此，使用这种方法，此查询 将支持
重叠的时间段。因此，每个“On事件”A都有其Event ID对应的事件，该事件将与对应的事件相关联Off Event ID。

开放式期限将受的支持LEFT JOIN。