Search code examples
sqlsql-serverdatediffavailability

Calculate the time difference in the same row dynamically

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:

enter image description here

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.

Solution

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