替代服务台查询的LAG功能

I have a query for my service desk , when i run it i get an error 'LAG' is not a recognized built in function name. I know the reason of this error because lag is introduced from SQL Server 2012 on ward. however my service desk db is SQL Server 2008. Is their any alternative to LAG function for the below query.

我有一个查询我的服务台,当我运行它我得到一个错误'LAG'不是一个公认的内置函数名称。我知道这个错误的原因,因为从病房的SQL Server 2012引入了滞后。但是我的服务台数据库是SQL Server 2008.对于以下查询,它们是否可以替代LAG函数。

enter code here

在此处输入代码

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

1 个解决方案

#1

Here is one way using Left Join and Row_Number window function

这是使用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' ) )