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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy