September 2, 2003 at 8:29 am
Hi all,
My source data looks like this...it basically represents how a network device has been behaving with time..
ID DEVICE_ID DATE STATE
12 227 2003-9-2 11:00 0
13 227 2003-9-2 11:05 1
14 227 2003-9-2 14:00 0
15 227 2003-9-2 14:05 1
From the above logs table I need to work out the "availability" stats by device. The result data needs to look like this. It basically shows for each device and for every hour how many seconds a device has been down for
Device_idUTCDOWN_SECS
2272003-9-2 11:0005
2272003-9-2 12:0000
2272003-9-2 13:0000
2272003-9-2 14:0005
September 2, 2003 at 9:55 am
Similar questions have been asked before in here. Trying to get this from memory.
First of all, you will need a table with all UTC values, call it Times.
Secondly, you will need to find the time differences.
SELECT S.Device_ID, S.Date,
DateDiff(seconds, S.Date, E.Date) AS DOWN_SECS
FROM (SELECT Device_Id, Date
FROM Devices
WHERE State = 0) S
INNER JOIN
(SELECT Device_ID, Date
FROM Devices
WHERE State = 1) E
ON S.Device_ID = E.Device_ID
AND S.Device_ID < E.Device_ID
WHERE NOT EXISTS
(SELECT 1
FROM Devices D
WHERE D.State = 1
AND D.Device_ID = E.Device_ID
AND D.Date > S.Date
AND D.Date < E.Date)
Now, you can join this set to the Times table, using a left outer join (Times to ResultSet). Do some summing and grouping by to get an hourly result...
September 2, 2003 at 5:35 pm
Thanks Peeters, trying out your suggestion now.
Regards
Uday
quote:
Similar questions have been asked before in here. Trying to get this from memory.First of all, you will need a table with all UTC values, call it Times.
Secondly, you will need to find the time differences.
SELECT S.Device_ID, S.Date,
DateDiff(seconds, S.Date, E.Date) AS DOWN_SECS
FROM (SELECT Device_Id, Date
FROM Devices
WHERE State = 0) S
INNER JOIN
(SELECT Device_ID, Date
FROM Devices
WHERE State = 1) E
ON S.Device_ID = E.Device_ID
AND S.Device_ID < E.Device_ID
WHERE NOT EXISTS
(SELECT 1
FROM Devices D
WHERE D.State = 1
AND D.Device_ID = E.Device_ID
AND D.Date > S.Date
AND D.Date < E.Date)Now, you can join this set to the Times table, using a left outer join (Times to ResultSet). Do some summing and grouping by to get an hourly result...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply