代码之家 › 专栏 › 技术社区 › Espo

如何扩展/透视此数据?

pivot aggregate sql-server-2005

4

Espo · 技术社区 · 14 年前

我有一组记录来跟踪系统可用性。样本数据:

System_ID   Event    DateOfEvent
1           Down     2010-05-01 13:20:10
1           Up       2010-05-01 13:25:19
1           Down     2010-05-05 10:12:12
1           Up       2010-05-06 14:10:16
2           Down     2010-05-05 20:22:22
2           Up       2010-05-06 23:20:26

如何将此数据转换为以下布局?

System_Id   DowntimeStart         DowntimeInSeconds
1           2010-05-01 13:20:10   309
1           2010-05-05 10:12:12   100684
2           2010-05-05 20:22:22   97084

1 回复 | 直到 14 年前

1

2

user474407 14 年前

您可以尝试以下操作:

Select et1.System_ID, et1.[Date] as DateDown, et2.[Date] as DateUp, DATEDIFF(s, et1.[Date], et2.[Date]) as DownForSeconds
    From EventTable et1
    Left Join EventTable et2 On et1.System_ID = et2.System_ID and et2.[Event] = 'Up'
Where 
et1.[Event] = 'Down'
and et2.[Date] = (Select Top 1 [Date] From EventTable Where System_ID = et2.System_ID and [Date] > et1.[Date])