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