Query to find the time difference between successive events

Question

Goal: Return a dataset showing time differences between successive events of the same type

Table Structure:

CREATE TABLE [dbo].[Event](
[EventID] [int] IDENTITY(1,1) NOT NULL,
[JobID] [int] NOT NULL, --FK to Job Table
[LastRun] [datetime] NOT NULL,
[LastRunEnd] [datetime] NULL,
[LastRunResult] [int] NOT NULL, --FK to Result
) ON [PRIMARY]
--Event ID is PK

Since the data comes from sequential events, LastRun will always increase for each job.
LastRunEnd could be null if the process that creates the entry fails fatally before generating the data.
LastRun will always be greater than all previous LastRunEnd

I'm trying to write a TSQL query that shows for each entry, the JobID, the LastRun and the LastRun of the previous entry. This will give me the gap between the two, and will thus detect missed runs.

My best shot so far:

select this.EventId as thisEvent,prev.Eventid as  prevEvent,
this.lastrun as thisRun,
prev.LastRun as prevRun,
datediff(hh,prev.LastRun,this.lastrun) as gap
from Event this
join (select 
     EventID, JobID,LastRun from Event ) prev on prev.jobid = 
     this.jobid and prev.EventID = (Select max(EventID) from Event 
                                    where LastRun < This.LastRun)
where this.LastRun > '2016-08-01' and job.jobid = 57

This seems to work for the rows it returns, however it returns too few rows. Using the where clause given, there are 15 events. I expected 14 rows returned, but there were only 3. The three that were returned looked like this:

thisEvent   prevEvent   thisRun          prevRun          gap
----------- ----------- ---------------- ---------------- ----
5172        5239        2016-08-01 16:16 2016-05-31 15:45 1489
5174        5239        2016-08-02 15:45 2016-05-31 15:45 1512
5176        5239        2016-08-03 15:45 2016-05-31 15:45 1536

I expected something like this:

thisEvent   prevEvent   thisRun          prevRun          gap
----------- ----------- ---------------- ---------------- ----
5176        5174        2016-08-03 15:45 2016-08-02 15:45 24
5174        5172        2016-08-02 15:45 2016-08-01 16:16 23

Clearly, all rows are picking up the same previous event. I'm also at a loss as to why only 3 rows are returned

Any help would be appreciated...

http://blog.sqlauthority.com/2011/11/15/sql-server-introduction-to-lead-and-lag-analytic-functions-introduced-in-sql-server-2012/ — Mike, Aug 27 '16 at 16:31

trincot · Answer 1 · 2016-08-27T16:41:08.470

You could use the lag analytic window function:

select   jobid,
         thisevent,
         prevevent,
         thisrun,
         prevrun,
         datediff(hh, thisrun, prevrun) as gap
from     (select  jobid,
                  eventid thisevent, 
                  lastrun thisrun,
                  lag(eventid) over (partition by jobid order by lastrun) prevevent,
                  lag(lastrun) over (partition by jobid order by lastrun) prevrun
          from    event 
          where   lastrun > '2016-08-01'
              and jobid = 57
         ) base
order by jobid, 
         thisrun

score 0 · Accepted Answer · answered Aug 27 '16 at 19:27

You can use the help of cte also..

creating sample data .

CREATE TABLE #Event
([EventID] [int] NOT NULL,
 [JobID] [int] NOT NULL, --FK to Job Table
 [LastRun] [datetime] NOT NULL
) ON [PRIMARY]

GO

INSERT INTO #Event 
VALUES (5172,1,'2016-08-01 16:16') 
  ,(5174,1,'2016-08-02 15:45') 
  ,(5176,1,'2016-08-03 15:45')
  ,(5239,1,'2016-05-31 15:45')

use the below query for the expeted result

With cte_1
     as (select  e.jobid,
              e.eventid thisevent, 
              e.lastrun thisrun,
              lag(e.eventid) over (partition by e.jobid order by e.lastrun) prevevent,
              lag(e.lastrun) over (partition by e.jobid order by e.lastrun) prevrun
      from    #event e
      where   e.lastrun > '2016-08-01')

    select   jobid,
             thisevent,
             prevevent,
             thisrun,
             prevrun,
             ABS(datediff(hh, thisrun, prevrun)) as gap
     FROM cte_1 
     ORDER BY jobid, thisrun

This is clearly dark magic. Even after reading the technet article linked by tricot, and the blog entry Mike so kindly linked, I'm unclear on what exactly it does. However, it **does** work, and it works well. I would still like to see the self-join solution though, but so many dbmses have such a function, the "old" way is hard to find. — Vorpal Swordsman, Aug 29 '16 at 00:37

Query to find the time difference between successive events

2 Answers2

Linked