在不同行上的字段之间执行DATEDIFF的查询

Question

我需要将DATEDIFF添加到一个查询中，该查询提供当前行的字段和前一行的相同字段之间的小时数。

编辑：{我是否应该按ROUTED_DTM DESC对整个查询进行排序，以及在DATEDIFF DESC中进行ORDER by？

其中一行的ROUTED_DTM为'2019-05-07 15:36:13.000'，上面一行的ROUTED_DTM为'2019-05-01 14:19:52.000‘。我期望AGE_IN_ROLE_DAY、AGE_IN_ROLE_HR、AGE_IN_ROLE_MIN、AGE_IN_ROLE_SEC依次为6、1、16和21。但是，我得到0，0，0，-2。}

SELECT c.ID,
       c.PAID_DT,
       DATEDIFF(dd,
                CASE WHEN c.ID_ADJ_FROM = '' THEN c.RECD_DT ELSE c.INPUT_DT END,
                CASE WHEN c.PAID_DT = '1/1/1753' THEN CONVERT(DATE,GETDATE()) ELSE c.PAID_DT END) + 1 AS DAYS_OLD
       DATEDIFF(dd, h.ROUTED_DTM, LAG(h.ROUTED_DTM) OVER (ORDER BY h.ROUTED_DTM DESC)) AS AGE_IN_ROLE_DAY,
       DATEDIFF(hh, h.ROUTED_DTM, LAG(h.ROUTED_DTM) OVER (ORDER BY h.ROUTED_DTM DESC)) AS AGE_IN_ROLE_HR,
       DATEDIFF(MM, h.ROUTED_DTM, LAG(h.ROUTED_DTM) OVER (ORDER BY h.ROUTED_DTM DESC)) AS AGE_IN_ROLE_MIN,
       DATEDIFF(ss, h.ROUTED_DTM, LAG(h.ROUTED_DTM) OVER (ORDER BY h.ROUTED_DTM DESC)) AS AGE_IN_ROLE_SEC,
       h.QUEUE_ID,
       h.QUEUE_DESC,
       h.ROLE_ID,
       h.ROLE_DESC,
       h.ROUTED_DTM

FROM table1 c
LEFT JOIN table2 h
    ON h.ID = c.ID
LEFT JOIN table 3 q
    ON q.QUEUE_ID = h.QUEUE_ID
LEFT JOIN table4 r
    ON r.ROLE_ID = h.ROLE_ID

ORDER BY c.ID, h.ROUTED_DTM DESC

我想在h.QUEUE_ID列之前添加一个DATEDIFF，它给出当前行的h.ROUTED_DTM与前一行的h.ROUTED_DTM之间的差异

目前，查询返回正确的结果，但是，我不确定如何将新的DATEDIFF添加到每一行。

Answer 1

您可以使用lag()

datediff(day, routed_dtm, lag(routed_dtm) over (order by routed_dtm))

您可能还需要在window子句中使用partition by c.id。