我对不同的资产/关闭事件的单一表。 我需要的启动和停止事件时间列表不使用光标。
资源：
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
这里的解决方案，概念证明给大家验证。
我注意到， 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将被首先关闭Off ，所以重叠时间将给出这个方法这个查询的支持 。 因此，每个On活动的A都会有Event ID ，将被链接到对应的Off Event ID 。
开放式期间将被支持LEFT JOIN 。
未经测试...
;WITH myCTE AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Item ORDER BY EventDate) AS rn
FROM MyTable
)
SELECT
M1.Item,
M1.EventDate AS Start,
M2.EventDate AS End
FROM
myCTE M1
JOIN
myCTE M2 ON M1.Item = M2.Item AND M1.rn+1 = M2.rn
WHERE
M1.Event = 'On'
AND
M2.Event = 'Off';
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')
;with a as
(
select item, eventdate, event, ROW_NUMBER() Over(Partition by Item order by EventDate) RN
from @YourTable
),
b as (
select item, eventdate, 0 e, rn, event from a where rn = 1
union all
select b.item, a.eventdate, case when b.event = 'Off' then b.e+1 else b.e end, a.rn, a.event
from b
join a on b.rn + 1 = a.rn and a.item = b.item
)
select item, min(eventdate) Start, max(eventdate) [End]
from b
group by item, e
order by item, e
