用于服务台查询的 LAG 功能的替代方法

Question

我对我的服务台有一个查询，当我运行它时，我收到一个错误"LAG"不是可识别的内置函数名称。我知道此错误的原因，因为滞后是从 SQL Server 2012 引入的。但是我的服务台数据库是SQL Server 2008。对于以下查询，他们是否可以替代 LAG 函数。

在此处输入代码

SELECT woh.workorderid 'Request ID', 
sd.Statusname 'Status',
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.historyid))/1000),'1970-01-01 00:00:00'), 100) AS "Previous Date",
CONVERT(VARCHAR(20), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00'), 100) AS "Current Date",
DATEDIFF(minute, dateadd(s,datediff(s,getutcdate(),getdate())+((LAG(woh.operationtime) OVER (ORDER BY woh.historyid)) /1000),'1970-01-01 00:00:00'), dateadd(s,datediff(s,getutcdate(),getdate())+(woh.operationtime/1000),'1970-01-01 00:00:00')) as "Minutes taken to Respond" 
FROM workorderhistory woh
LEFT JOIN workorderhistorydiff wohd ON wohd.Historyid=woh.Historyid 
LEFT JOIN Statusdefinition sd ON sd.Statusid=CAST(wohd.Current_value AS INT) 
LEFT JOIN workorder wo ON wo.workorderid = woh.workorderid
WHERE (((woh.Operation='CREATE' AND wohd.Columnname IS NULL) OR woh.Operation='RESOLVED' OR woh.Operation='CLOSE') OR (woh.Operation='UPDATE' AND wohd.Columnname='STATUSID'))

Answer 1

这是使用 Left Join 和Row_Number窗口函数的一种方法

;WITH cteworkorderhistory
     AS (SELECT *,
                Row_number() OVER(ORDER BY historyid) AS Rn
         FROM   workorderhistory)
SELECT woh.workorderid                                                                                                                                                                                                                    'Request ID',
       sd.Statusname                                                                                                                                                                                                                      'Status',
       CONVERT(VARCHAR(20), Dateadd(s, Datediff(s, Getutcdate(), Getdate()) + ( ( b.operationtime ) / 1000 ), '1970-01-01 00:00:00'), 100)                                                                                                AS "Previous Date",
       CONVERT(VARCHAR(20), Dateadd(s, Datediff(s, Getutcdate(), Getdate()) + ( woh.operationtime / 1000 ), '1970-01-01 00:00:00'), 100)                                                                                                  AS "Current Date",
       Datediff(minute, Dateadd(s, Datediff(s, Getutcdate(), Getdate()) + ( ( b.operationtime ) / 1000 ), '1970-01-01 00:00:00'), Dateadd(s, Datediff(s, Getutcdate(), Getdate()) + ( woh.operationtime / 1000 ), '1970-01-01 00:00:00')) AS "Minutes taken to Respond"
FROM   cteworkorderhistory woh
       LEFT JOIN cteworkorderhistory b
              ON b.Rn = woh.Rn - 1
       LEFT JOIN workorderhistorydiff wohd
              ON wohd.Historyid = woh.Historyid
       LEFT JOIN Statusdefinition sd
              ON sd.Statusid = Cast(wohd.Current_value AS INT)
       LEFT JOIN workorder wo
              ON wo.workorderid = woh.workorderid
WHERE  ( ( ( woh.Operation = 'CREATE'
             AND wohd.Columnname IS NULL )
            OR woh.Operation = 'RESOLVED'
            OR woh.Operation = 'CLOSE' )
          OR ( woh.Operation = 'UPDATE'
               AND wohd.Columnname = 'STATUSID' ) )