How can i aggrategate/pivot this data?

Question

I have a set of records that keeps track of system availability. Sample data:

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

How can I convert this data into the following layout?

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

Answer 1

You can try this:

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])