CODEWITHSUNDEEP
sqlsql-server-2008t-sql
RussAwesome
RussAwesome

Reputation: 464

Get lengths of times since last incident

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

Upvotes: 1

Views: 184

Answers (2)

Vasily
Vasily

Reputation: 5782

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

enter image description here

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:

enter image description here

Upvotes: 1

Rajesh Bhat
Rajesh Bhat

Reputation: 811

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

Upvotes: 0

Related Questions