逾期 -10，表示逾期 10 分钟。将'-10'换成逾期 10 分钟

Question

CASE WHEN Color LIKE '%RED%' THEN (10 - (DATEDIFF(MINUTE,Calltime,GETDATE()))) 

有时可能会给出-10的值。我的问题是当我得到一个值<0，如何将该值更改为过期10分钟。

Answer 1

您可以嵌套CASE语句，但由于现在将文本引入整数值，您必须将所有输出强制转换为字符串：

CASE WHEN Color LIKE '%RED%' THEN
CASE WHEN (10 - (DATEDIFF(MINUTE,Calltime,GETDATE()))) >= 0 THEN CAST((10 - (DATEDIFF(MINUTE,Calltime,GETDATE()))) AS VARCHAR(MAX))
ELSE CAST(ABS((10 - (DATEDIFF(MINUTE,Calltime,GETDATE())))) AS VARCHAR(MAX)) + ' minutes overdue'
END
ELSE -- Additional SQL for when color is not red
END

Answer 2

只需在CASE中使用另一个CASE END。试试这个：

CASE WHEN Color LIKE '%RED%' THEN (CASE WHEN (10 - (DATEDIFF(MINUTE,Calltime,GETDATE()))) < 0 THEN 10 ELSE (10 - (DATEDIFF(MINUTE,Calltime,GETDATE()))) END)

Answer 3

我更喜欢计算lateral join中的公共值，而不是多次重复我的逻辑。

DECLARE @Test TABLE (CallTime DATETIME, Color VARCHAR(12));
INSERT INTO @Test (Color, CallTime)
SELECT 'RED', DATEADD(MINUTE, -5, GETDATE())
UNION ALL SELECT 'RED', DATEADD(MINUTE, -12, GETDATE())
UNION ALL SELECT 'BLUE', DATEADD(MINUTE, -5, GETDATE());
SELECT
CASE WHEN T.Color LIKE '%RED%' THEN
CASE WHEN C.CallDifference >= 0 THEN CAST(C.CallDifference AS VARCHAR(MAX))
ELSE CAST(ABS(C.CallDifference) AS VARCHAR(MAX)) + ' minutes overdue'
END
ELSE 'BLUE'
END
FROM @Test T
CROSS APPLY (VALUES(10 - (DATEDIFF(MINUTE,T.Calltime,GETDATE())))) as C (CallDifference);

感谢e_i_pi提供的逻辑代码。