Geek Answers Handbook

How can I aggregate / expand this data?

I have a set of records that track 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 to the next 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
+4
source share
1 answer

You can try the following:

 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])
+2
source

More articles:


All Articles