SQL计算ORD.PicksetConfirmTS与PAN.[1ST PHYSICAL SCAN]时间差求助

Hey there! I see you're trying to calculate the time difference between ORD.PicksetConfirmTS and PAN.[1ST PHYSICAL SCAN] in SQL, and you need to properly handle negative time values without that messy -1--12-456 formatting you were getting before. Let's fix this up!

The Problem with Your Original Approach #

Your initial CONCAT method fails because each DATEDIFF call returns its own positive/negative value. For example, if PicksetConfirmTS is earlier than 1ST PHYSICAL SCAN, the day, hour, and minute differences all come back negative—leading to that broken -1--12-... string. Instead, we need to check the overall sign of the time difference first, then format the absolute value with the correct sign attached.

Solution: Match Your Excel Logic in SQL #

We'll use a CASE statement to replicate the logic from your Excel formula:

1. Check if the total time difference is positive or negative
2. Format the absolute value of the difference into dd - hh:mm
3. Prepend a negative sign only if the original difference is negative

Here's how to integrate this into your existing query:

SELECT 
    PAN.[FULL PARCEL ID], 
    PAN.[12 DIGIT PARCEL ID], 
    PAN.[NO OF PARCELS], 
    PAN.[SERVICE ID], 
    PAN.[PAN PROCESS DATE TIME], 
    PAN.[Late Time Measure DD:HH:MM], 
    PAN.[1ST PHYSICAL SCAN], 
    PAN.[HUB SCAN], 
    PAN.[SORT LOCATION], 
    PAN.[PAN STATUS], 
    PAN.[PAN FUNCTION], 
    RLZ.ReportGroup, 
    ORD.PicksetNo, 
    ORD.PicksetPrintTS, 
    TASK.TaskStart, 
    TASK.TaskEnd, 
    ORD.PicksetConfirmTS, 
    ORD.EarliestPickDate,
    -- New time difference calculation
    CASE
        -- Handle NULL values first to avoid errors
        WHEN ORD.PicksetConfirmTS IS NULL OR PAN.[1ST PHYSICAL SCAN] IS NULL THEN 'N/A'
        WHEN DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS) >= 0 THEN
            CONCAT(
                -- Calculate total days
                ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) / 86400,
                ' - ',
                -- Calculate hours (pad with leading zero)
                RIGHT('0' + CAST((ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) % 86400) / 3600 AS VARCHAR), 2),
                ':',
                -- Calculate minutes (pad with leading zero)
                RIGHT('0' + CAST(((ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) % 86400) % 3600) / 60 AS VARCHAR), 2)
            )
        ELSE
            CONCAT(
                '-',
                ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) / 86400,
                ' - ',
                RIGHT('0' + CAST((ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) % 86400) / 3600 AS VARCHAR), 2),
                ':',
                RIGHT('0' + CAST(((ABS(DATEDIFF(SECOND, PAN.[1ST PHYSICAL SCAN], ORD.PicksetConfirmTS)) % 86400) % 3600) / 60 AS VARCHAR), 2)
            )
    END AS PicksetConfirm_1stScan_TimeDiff
FROM CHDS_Sandbox.dbo.PANTEST PAN 
LEFT JOIN CHDS_Common.dbo.OMOrder ORD ON ORD.AddressBarcode = PAN.[FULL PARCEL ID] 
LEFT JOIN CHDS_Common.dbo.RackListZone RLZ ON RLZ.Loc = ORD.ProductWhsLocation 
LEFT JOIN CHDS_Common.dbo.TaskScan TASK ON TASK.Pickset = ORD.PicksetNo

How This Works #

• We use DATEDIFF(SECOND, ...) to get the total time difference in seconds—this gives us a single positive/negative value to check the overall sign.
• ABS() converts the total seconds to a positive number for consistent formatting.
• We calculate days, hours, and minutes from the absolute second count, using RIGHT('0' + ..., 2) to ensure single-digit hours/minutes are padded with a leading zero (e.g., 5 becomes 05).
• The CASE statement adds a negative sign only when the original time difference is negative, matching your Excel formula's behavior exactly.

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