如何在SQL中计算连续行日期的时间差（含现有业务SQL代码）

Hey there! I see you need to compute the time difference between the current row's date and the previous row's date (like the highlighted yellow section in your reference image). Let's fix this up in your existing query step by step.

Core Approach #

We'll use two key SQL functions here to get the result you need:

• LAG(): This window function lets us fetch the action_Date value from the immediately preceding row within the same voucher_no group (ordered by date to maintain chronological order).
• DATEDIFF(): This calculates the difference between two dates, with flexible units like hours (HOUR) or days (DAY).

Modified Full Query #

First, I fixed a minor syntax issue in your original CTE structure and adjusted the date conversion to keep action_Date as a datetime type (critical for accurate date calculations—your original code converted it to VARCHAR, which breaks arithmetic operations). Here's the updated code with the time difference calculation:

WITH cte AS ( 
    SELECT 
        m.voucher_no, 
        -- Convert directly to datetime for calculation compatibility
        CONVERT(datetime, f.action_Date, 109) AS action_date,
        f.col1_Value,
        f.col3_value,
        f.col4_value,
        f.comments,
        f.distr_user,
        f.wf_status,
        f.action_code,
        f.wf_user_id 
    FROM attdetailmap m 
    LEFT JOIN awftaskfin f ON f.oid = m.oid AND f.client = 'PC' 
    WHERE f.action_Date != '' AND action_date BETWEEN '$?datef' AND '$?datet' 
), 
cte2 AS ( 
    SELECT 
        *, 
        ROW_NUMBER() OVER(PARTITION BY voucher_no, action_Date, distr_user, wf_Status, wf_user_id ORDER BY voucher_no) AS row_no_1 
    FROM cte 
) 
SELECT 
    DISTINCT(v.dim_value) AS resid,
    c.voucher_no,
    c.action_Date,
    -- Calculate time difference in hours (replace HOUR with DAY for daily difference)
    DATEDIFF(HOUR, 
             LAG(c.action_Date) OVER(PARTITION BY c.voucher_no ORDER BY c.action_Date), 
             c.action_Date) AS time_diff_hours,
    -- Optional: Add daily difference if needed
    -- DATEDIFF(DAY, LAG(c.action_Date) OVER(PARTITION BY c.voucher_no ORDER BY c.action_Date), c.action_Date) AS time_diff_days,
    c.col4_value,
    c.comments,
    c.distr_user,
    v.description,
    c.wf_status,
    c.action_code,
    c.wf_user_id,
    v1.description AS name,
    r.rel_value AS pay_office,
    r1.rel_value AS site 
FROM cte2 c 
LEFT OUTER JOIN aagviuserdetail v ON v.user_id = c.distr_user 
LEFT OUTER JOIN aagviuserdetail v1 ON v1.user_id = c.wf_user_id 
LEFT OUTER JOIN ahsrelvalue r ON r.resource_id = v.dim_Value AND r.rel_Attr_id = 'P1' AND r.period_to = '209912' 
LEFT OUTER JOIN ahsrelvalue r1 ON r1.resource_id = v.dim_Value AND r1.rel_Attr_id = 'Z1' AND r1.period_to = '209912' 
WHERE c.row_no_1 = '1' AND r.rel_value LIKE '$?site1' AND voucher_no LIKE '$?trans' 
ORDER BY c.voucher_no, c.action_Date;

Key Notes #

1. Date Conversion: Keeping action_date as a datetime type ensures we can perform accurate arithmetic operations on dates.
2. Window Function Logic: The LAG() function is partitioned by voucher_no to ensure we only compare rows within the same voucher, and ordered by action_Date to maintain chronological order.
3. Handling NULLs: The first row in each voucher_no group won't have a previous row, so time_diff_hours will return NULL. If you want to replace this with a default value (like 0), wrap the DATEDIFF call in ISNULL():

   ISNULL(DATEDIFF(HOUR, LAG(c.action_Date) OVER(PARTITION BY c.voucher_no ORDER BY c.action_Date), c.action_Date), 0) AS time_diff_hours
   

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