SQL的新功能，必须找到同一列的第一行和第二行的小时数差异

debugcn 发表于 Dev

萨拉瓦南

例如。比较invoice_status_change_datetime的第一行和第二行，直到进入不同状态为止，必须捕获以小时为单位的持续时间。

**AP_WORK_ID    INVOICE_STATUS  INVOICE_STATUS_CHANGE_DATETIME   LAST_UPDATED_USER**
  1060565       Assigned        **2020-01-27 07:17:57.837**          xxxxxx
  1060565       Assigned        **2020-01-27 10:17:57.837**          yyyyyy
  1060565       In Progress       2020-01-29 01:08:56.943            xxxxxx
  1060565       Rejected          2020-01-28 07:17:57.837            xxxxxx
  1060565       Hold              2020-01-28 10:17:57.837            yyyyyy
  1060565       Closed            2020-01-29 01:08:56.943            xxxxxx

但是结果应该如下所示，因为我想将此结果与其他联接条件合并

**AP_WORK_ID    Assigned_hrs  In Progress_hrs  Query_Resolved_hrs Rejected_hrs hold_hrs closed_hrs**
  1060565       24            3 hrs            null               10hrs        5hrs     null

我使用lead（）尝试了代码，我也得到了以小时为单位的输出，但是我不知道如何以上述格式进行转换：

SELECT isc.INVOICE_STATUS,
    isc.INVOICE_STATUS_CHANGE_DATETIME,
    DATEDIFF(HH, isc.INVOICE_STATUS_CHANGE_DATETIME, LEAD(isc.INVOICE_STATUS_CHANGE_DATETIME) OVER(
    ORDER BY isc.INVOICE_STATUS_CHANGE_DATETIME)) AS status_change_Hours       
FROM INVOICE_STATUS_CHANGE ISC
where isc.AP_WORK_ID = 1060565
GROUP BY isc.INVOICE_STATUS, isc.INVOICE_STATUS_CHANGE_DATETIME

我知道这有点复杂，可以熟练掌握SQL的人知道如何实现这一点

专线小巴

我正在考虑lead()和条件聚合：

select
    ap_work_id,
    sum(case when invoice_status = 'Assigned'    
        then datediff(hour, change_dt, next_change_dt) end) assigned_hrs,
    sum(case when invoice_status = 'In Progress' 
        then datediff(hour, change_dt, next_change_dt) end) in_progress_hrs,
    sum(case when invoice_status = 'Rejected'
        then datediff(hour, change_dt, next_change_dt) end) rejected_hrs,
    sum(case when invoice_status = 'Hold' 
        then datediff(hour, change_dt, next_change_dt) end) hold_hrs,
    sum(case when invoice_status = 'Closed' 
        then datediff(hour, change_dt, next_change_dt) end) closed_hrs
from (
    select 
        ap_work_id,
        invoice_status,
        invoice_status_change_datetime change_dt,
        lead(invoice_status_change_datetime) 
            over(partition by ap_work_id order by invoice_status_change_datetime) next_change_dt
    from mytable t
) t
group by ap_work_id

DB Fiddle上的演示：

ap_work_id | Assigned_hrs | in_progress_hrs | rejected_hrs | hold_hrs | close_hrs 
---------：| -----------：| | --------------：| -----------：| | -------：| ---------：
   1060565 | 18 | 6 | 3 | 15 |       空值