查询以查找连续事件之间的时间差

小编典典

查询以查找连续事件之间的时间差

sql

目标：返回一个数据集，该数据集显示相同类型的连续事件之间的时间差

表结构：

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

由于数据来自顺序事件，因此对于每个作业，LastRun将始终增加。
如果创建条目的过程在生成数据之前致命失败，则LastRunEnd可以为null。
LastRun将始终大于所有以前的LastRunEnd

我正在尝试编写一个TSQL查询，该查询显示每个条目的JobID，上一个条目的LastRun和LastRun。这将给我两者之间的空白，从而检测出错过的比赛。

到目前为止我最好的镜头：

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

这似乎适用于它返回的行，但是它返回的行太少。使用给定的where子句，有15个事件。我期望返回14行，但是只有3行。返回的三行看起来像这样：

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

我期望这样的事情：

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

显然，所有行都在拾取相同的先前事件。我也茫然为什么只返回3行

任何帮助，将不胜感激…

阅读 176

2021-05-30

共1个答案

小编典典

您也可以使用cte的帮助。

创建样本数据。
```
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’)

使用以下查询查询删除结果

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

2021-05-30