Calculate the time difference in the same row dynamically

Question

Is there any way to calculate the time difference in SQL between rows within the same column based on the 'DOWN' and 'UP' values like this:

There are 3 scenarios(that I'm aware of):

Yellow, Orange and Green: there is a state_id 2(down) and after that a state_id 5(up), so the time difference needs to be calculated between the two rows;
Blue: there are multiple state_id 2(down) and after that one state_id 5(up), so the time difference needs to be calculated between the first row and last row;
Red: there is only a state_id 2(down) because it is still down with any update, so the time difference needs to be calculated till the end of the month.

I hope you can help me out.

What version of SQL Server are you using? – SS_DBA Jun 01 '17 at 15:09 — SS_DBA, Jun 01 '17 at 15:09

LukStorms · Accepted Answer · 2017-06-01T18:15:22.837

Was first considering to use LAG for this.

But using a cummulative SUM, and the window version of MIN works also for more than 2 DOWN's:

-- test reference data
declare @State table (id int, state varchar(4));
insert into @State (id, state) values 
(2,'DOWN'),
(5,'UP')

-- test data, using a table variable
declare @AlertState table (alert_id int identity(1,1), host_id int, state_time datetime, state_id int);
insert into @AlertState (host_id, state_time, state_id) values 
(119, GetDate()-0.32, 2),
(119, GetDate()-0.31, 5),
(119, GetDate()-0.24, 2),
(119, GetDate()-0.23, 2),
(119, GetDate()-0.22, 2),
(119, GetDate()-0.21, 5),
(119, GetDate()-0.15, 5),
(119, GetDate()-0.11, 2);

-- The query
select alert_id, host_id, state_time, state_id,
 diff_min = (
   case 
   when state_id = 5 then 
     datediff(minute, min(state_time) over (partition by host_id, stategroup), state_time)
   when state_id = 2 and stategroup is null then
     datediff(minute, state_time, cast(EOMONTH(GetDate()) as datetime)+1)
   end),
 s.state
from (
    select alert_id, host_id, state_time, state_id,
    sum(case state_id when 5 then 1 end) over (partition by host_id order by state_time desc) as stategroup
    from @AlertState
    where state_id in (2,5)
) q
left join @State s on s.id = q.state_id
order by state_time, alert_id;

score 0 · Answer 2 · answered Jun 01 '17 at 15:20

The way I did this before is

Select a.state_time as downtime, 
    (
        select min(inner.state_time) from tablename downentry where
        inner.state_time > outer.state_time and downentry.state='UP'
    ) as uptime

from tablename upentry 
where state = 'DOWN'

Then you need to find the datediff between them, and if uptime is null, the datediff between downtime and 'endofmonth'

It's potentially quite poor performing, so I always wrote the answer out to a data warehouse, but think it gives the results you're asking for.

score 0 · Answer 3 · answered Jun 01 '17 at 17:32

SQL2012+

You could try following solution:

SELECT y.group_id, host_id = MIN(host_id), start_time = MIN(state_time), end_time = MAX(state_time), diff_minute = DATEDIFF(MINUTE, MIN(state_time), MAX(state_time))
FROM (
    SELECT *, group_id = SUM(x.new_group_start) OVER(ORDER BY x.host_id, x.state_time)
    FROM (
        SELECT  *, new_group_start = IIF(a.state_id = 'DOWN' AND ISNULL(LAG(a.state_id) OVER(ORDER BY a.host_id, a.state_time), 'UP') = 'UP', 1, 0)
        FROM    @Alerts a
    ) x
) y
GROUP BY y.group_id
ORDER BY y.group_id

Demo

Calculate the time difference in the same row dynamically

3 Answers3