来自单个列的 T-SQL 开始和结束日期时间

T-SQL Start and end date times from a single column(来自单个列的 T-SQL 开始和结束日期时间)

问题描述

我有一个不同资产的开/关事件表.我需要得到一个 start & 列表在不使用游标的情况下停止事件时间.

I have a single table of on/off events for different assets. I need to get a list of start & stop event times without using a cursor.

来源:

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

推荐答案

这里是解决方案，有概念证明供大家验证.

Here's the solution, with proof of concept for everyone to verify.

我注意到 C 的 Off 事件被标记为新的 On.我解决了这个问题，但这也让我编写了一个解决方案，允许事件已经开始但尚未完成，所以我包含了一个开放式事件 D.

I noticed that the Off event for C was marked as a new On. I fixed that, but that also led me to coding a solution that would allow having an event that had started but not finished, so I included an open ended event D.

此外，我的解决方案适用于重叠的时期.

Additionally, my solution works with overlapping periods.

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 更改时重新启动.

We divide the data set in 2: On events and Off events. Each containing a numbered row which restarts when Item changes.

基本上，我们有先进先出:第一个 On 将被第一个 Off 关闭，因此将支持重叠周期 通过这个查询给出这种方法.因此，A 的每个 On 事件都会有它的 Event ID，它将链接到对应的 Off >事件 ID.

Basically, we have a first in first out: The first On will be closed by the first Off, so overlapping periods will be supported by this query given this approach. So each On Event for A will have its Event ID, which will be linked to the correspondent Off Event ID.

LEFT JOIN 将支持开放式期间.

这篇关于来自单个列的 T-SQL 开始和结束日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！