SQL Server 2008中替代LAG函数的服务台查询方案咨询

Since SQL Server 2008 doesn't support the LAG() window function (introduced in SQL Server 2012), we can replicate its behavior using a self-join with row numbering. This approach lets us fetch the previous row's operationtime value by ordering on historyid, just like your original query intended.

Here's the adjusted query that works with SQL Server 2008:

WITH OrderedHistory AS (
    SELECT 
        woh.workorderid,
        woh.operationtime,
        woh.historyid,
        woh.Operation,
        wohd.Columnname,
        wohd.Current_value,
        sd.Statusname,
        wo.workorderid AS parent_workorderid,
        -- Assign sequential row numbers ordered by historyid to link each row to its predecessor
        ROW_NUMBER() OVER (ORDER BY woh.historyid) AS rn
    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')
)
SELECT 
    t1.workorderid AS 'Request ID',
    t1.Statusname AS 'Status',
    -- Pull previous operationtime from the row with rn = current rn - 1
    CONVERT(VARCHAR(20), DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (t2.operationtime/1000), '1970-01-01 00:00:00'), 100) AS "Previous Date",
    CONVERT(VARCHAR(20), DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (t1.operationtime/1000), '1970-01-01 00:00:00'), 100) AS "Current Date",
    DATEDIFF(minute, 
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (t2.operationtime/1000), '1970-01-01 00:00:00'),
        DATEADD(s, DATEDIFF(s, GETUTCDATE(), GETDATE()) + (t1.operationtime/1000), '1970-01-01 00:00:00')
    ) AS "Minutes taken to Respond"
FROM OrderedHistory t1
-- Self-join to get the immediate previous row
LEFT JOIN OrderedHistory t2 ON t2.rn = t1.rn - 1
ORDER BY t1.rn;

Quick Breakdown: #

• We use a CTE (OrderedHistory) to first assign a sequential row number (rn) to each record, ordered by historyid—this matches the ordering logic from your original LAG() clause.
• The self-join on t2.rn = t1.rn -1 fetches the previous row's data, acting as a direct replacement for LAG().
• Just like LAG(), the first row in the result set will have NULL values for "Previous Date" and "Minutes taken to Respond" (since there's no prior row to reference).
• All your original filtering, date conversion, and join logic remains exactly as you wrote it—we only swapped out the LAG() functionality.

内容的提问来源于stack exchange，提问作者Biswa