我有一個 SQL 查詢，如下所示。我想根據與指定日期與當前日期不同的分鐘數獲得正確的檔案計數，但我注意到查詢不會計算正確的檔案編號。
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%')
下面是我的表，上面的SQL查詢結果總是回傳2，按理應該回傳1，有什么問題請指教？假設當前日期是2022-05-27 9:20:00
| 檔案號碼。 | 指定日期 | 運輸 |
|---|---|---|
| 4242423 | 2022-05-27 10:20:33 | 空氣 |
| 5675756 | 2022-06-27 18:20:33 | 空氣 |
uj5u.com熱心網友回復：
如果我理解正確，您需要在僅考慮1小時時差時更改案例陳述句中的邏輯
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%')
uj5u.com熱心網友回復：
count將計算所有非空值，因此對于 else 回傳 NULL 而不是零。另外，也許datediff 序列可能需要交換日期時間值，因為您可能會產生負值，但這取決于資料，所以我不確定后一點。
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%')
注意：當我測驗 getdate() 時回傳2022-06-27 05:16:53.047
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/495635.html