Get lengths of times since last incident

Question

I have a table called ITIncidents tracking the instances and length of time of incidents.

By getting the maximum EndDate and comparing to GetDate() I can get the length of time since the last incident ("It has been 3 days, 6 hours, 56 seconds since the last incident").

I would like to work out the previous 'record' of the amount of time between the end of one incident and the start of another.

Example schema and data at: http://sqlfiddle.com/#!6/6ec2a/1

I have looked at this question and tried to fit this code:

WITH    rows AS
    (
    SELECT  *, ROW_NUMBER() OVER (ORDER BY DataDate) AS rn
    FROM    mytable
    )
SELECT  DATEDIFF(second, mc.DataDate, mp.DataDate)
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

to the situation, but because I am calculating the difference between the end date on one row and the start date on another, I am struggling to even use DATEDIFF.

In the SQLFiddle example the date of the last entry's EndDate is 2017-11-01 00:10:00, so as of Nov 8th 2017 00:00:00, it has been 6 days, 23 hours, 50 minutes since the last incident, but the previous 'record' was the length of time between the incident that ended on the 2017-10-09 15:10:00 and the incident that started 2017-11-01 00:00:00

You want to calculated the duration of each incident or the duration between the end of each incident till now? — gotqn, Nov 08 '17 at 10:19
Gotqn: I want to find the lengths of times between the end of one incident on one row, and the start of an incident on another. So we can say ‘we’ve not had an incident for 6 days, but the previous record was 18 days’ for example. — RussAwesome, Nov 08 '17 at 10:33

Vasily · Accepted Answer · 2017-11-08T23:07:55.793

try this:

  select t.id, f.Period
  from (select --top 1 --uncomment here 
               l.id, 
               m = (case when f.m < 0 then 0 else f.m end)
        from itincidents as l 
              outer apply (select top 1 StartDate
                           from itincidents as r 
                           where l.id < r.id
                           order by r.id
                          ) as r
              cross apply (select datediff(minute, l.EndDate,  
                            coalesce(r.StartDate, getdate())) as m
                          ) as f
        --order by l.id desc --and here to get only last record
       ) as t 
       cross apply 
           (select 
              case when t.m < 60 then convert(varchar(10),t.m) + ' Min'
                   when t.m < 1440 then convert(varchar(10),t.m/60) + ' Hr, ' + 
                                        convert(varchar(10),t.m%60) + ' Min'
                   else convert(varchar(10),t.m/1440) + ' Days, ' + 
                        convert(varchar(10),(t.m%1440)/60) + ' Hr, ' + 
                        convert(varchar(10), (t.m%1440) % 60) + ' Min'
              end as [Period]
           ) as f

tested on your dataset with breaking sequence (one record is deleted) :

to get the difference since the last incident:

select case when t.m < 60 then convert(varchar(10),t.m) + ' Min'
            when t.m < 1440 then convert(varchar(10),t.m/60) + ' Hr, ' + 
                                 convert(varchar(10),t.m%60) + ' Min'
            else convert(varchar(10),t.m/1440) + ' Days, ' + 
                 convert(varchar(10),(t.m%1440)/60) + ' Hr, ' + 
                 convert(varchar(10), (t.m%1440) % 60) + ' Min'
       end as [Period]
from (select datediff(minute, max(l.EndDate), getdate()) as m
      from itincidents as l) as t

tested on your dataset:

That is certainly incredibly close. I think I may need to order the original select somehow by Enddate or startddate so that it isn't relying on `id-1` to pick the order of the incidents, and I also wouldn't need to display the final difference between the max enddate and getdate(), but other than that it seems very close so far, thank you. — RussAwesome, Nov 08 '17 at 12:51
Also, if the ids are not sequential, or some are missing (because an incident has been removed, for example), will this still work? — RussAwesome, Nov 08 '17 at 12:54
@RussAwesome I've updated my answer, this variant will works with breaking sequence too. Also, if you need to display the final difference - just uncomment commented rows or use the last provided code (to get the difference since the last incident). — Vasily, Nov 08 '17 at 23:11

score 0 · Answer 2 · answered Nov 08 '17 at 11:04

0

Is this what you are looking for?

SELECT A.*, CONCAT(datediff(d,A.enddate,B.StartDate),' days ',datediff(HOUR,A.enddate,B.StartDate), ' hours ',datediff(MINUTE,A.enddate,B.StartDate), ' minutes') DateDifference
 FROM [ITIncidents] A INNER JOIN [ITIncidents] B on B.id=A.id+1

answered Nov 08 '17 at 11:04

Rajesh Bhat

791
3
8
20

`concat` doesn't supported by sql-server-2008 – Vasily Nov 08 '17 at 11:29
Vasily is correct that `CONCAT` is not supported, but it looks like this answer assumes that the incidents will be sequential (looking at the join of `B.id = A.id+1`), whereas I would like to dynamically work out the longest difference between all the end dates and the next subsequent start date using time as a sequence rather than ID. – RussAwesome Nov 08 '17 at 12:06

Get lengths of times since last incident

2 Answers2