Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Time Difference with next available row

Status
Not open for further replies.

Cap2010

Programmer
Mar 29, 2000
196
CA
Below is the field in the table
as
event_uuid
type_string
trap_name
event_time

Want to find difference from downtime with uptime i.e. diff. Have to subtract from next row when it has uptime. below sql is not working what is wrong

SELECT a.type_string,
b.trap_name,
CASE when
b.trap_name='OV_IF_Down' then b.event_time
else ''
end downtime,
CASE when
b.trap_name='OV_IF_Up' then b.event_time
else ''
end uptime
case when
downtime - uptime <> 0 then downtime-uptime
else ''
end Duration

into #new_table
FROM nnm_event_varbinds a,
nnm_event_detail b
WHERE a.event_uuid=b.event_uuid
AND (b.trap_name='OV_IF_Up'
OR b.trap_name='OV_IF_Down')
AND b.event_time
like '2004-05-21%'
ORDER BY
b.nodename,
a.type_string,
b.event_time;


Need below output
OUTPUT
IF name uptime Downtime diff
Se0 OV_IF_Down 5/21/2004 17:47
Se0 OV_IF_Up 5/21/2004 19:31 1:44
Se2/0 OV_IF_Down 5/21/2004 19:31
Se2/0 OV_IF_Up 5/21/2004 19:35 0:04
Se2/0 OV_IF_Down 5/21/2004 12:52
Se2/1 OV_IF_Up 5/21/2004 19:41 6:49
Se3/1 OV_IF_Down 5/21/2004 12:36
Se3/0:11 OV_IF_Down 5/21/2004 12:47
Se3/0:11 OV_IF_Up 5/21/2004 17:42 4:55
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top