SQL query - CASE WHEN with DATEDIFF-CodePudding

I have a SQL query as shown below. I would like to get the correct document count based on minutes different from the assigned date vs the current date, but I noticed the query will not count the correct Document No.

SELECT 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 60 
                  THEN [Document No.] 
                  ELSE 0 
          END) AS [Yet to Pick > 1 hour], 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) > 120 
                  THEN [Document No.] 
                  ELSE 0 
          END) AS [Yet to Pick > 2 hours]
FROM 
    tb_name
WHERE 
    ([Shipment] LIKE '%AIR%' OR [Shipment] LIKE '%COURIER%')

Below is my table, the above SQL query result will always return as 2, by right it should return as 1, please advise if there is anything wrong? Let's say current date is 2022-05-27 9:20:00

Document No.	Assigned Date	Shipment
4242423	2022-05-27 10:20:33	AIR
5675756	2022-06-27 18:20:33	AIR

CodePudding user response：

If I understand correctly you Need to change the logic in the case statement when considering only 1 hour time difference

SELECT 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) >= 60 
                   AND DATEDIFF(MINUTE, [Assigned Date], GETDATE()) < 120
                  THEN [Document No.] 
                  ELSE NULL 
          END) AS [Yet to Pick > 1 hour], 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, [Assigned Date], GETDATE()) >= 120 
                  THEN [Document No.] 
                  ELSE NULL 
          END) AS [Yet to Pick > 2 hours]
FROM tb_name
WHERE ([Shipment] LIKE '%AIR%' OR [Shipment] LIKE '%COURIER%')

CodePudding user response：

count will count all non-null values, so for else return NULL instead of zero. Plus, maybe the datediff sequence might need swapping the datetime values as you may be producing negative values, but this is data dependent so I am not certain on this latter point.

SELECT 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, GETDATE(),[Assigned Date]) > 60 
                  THEN [Document No.] 
                  ELSE NULL 
          END) AS [Yet to Pick > 1 hour], 
    COUNT(CASE 
              WHEN DATEDIFF(MINUTE, GETDATE(), [Assigned Date]) > 120 
                  THEN [Document No.] 
                  ELSE NULL
          END) AS [Yet to Pick > 2 hours]
    --, getdate() timenow
FROM 
    tb_name
WHERE 
    ([Shipment] LIKE '%AIR%' OR [Shipment] LIKE '%COURIER%')

nb: when I tested getdate() returned 2022-06-27 05:16:53.047