CASE
WHEN Isconverted = 'TRUE' THEN DATEDIFF(DAY, Task_CreatedDate, OPP.CreatedDate)
WHEN Isconverted = 'FALSE' THEN DATEDIFF(DAY, Task_CreatedDate, A.Archived_Date)
WHEN A.Archived_Date IS NULL THEN DATEDIFF(DAY, Task_CreatedDate, GETDATE())
ELSE 'NULL'
END AS 'Age'
** 当语句不计算第三行时,Archive_Date为空时,上述情况我需要用当前日期填充年龄列 - Task_CreatedDate **
我想你想要:
(CASE WHEN Isconverted = 'TRUE'
THEN DATEDIFF(DAY, Task_CreatedDate, OPP.CreatedDate)
ELSE DATEDIFF(DAY, Task_CreatedDate, COALESCE(A.Archived_Date, GETDATE()))
END) AS Age
我假设对于A.Archived_Date为空的情况,Isconvert要么为真,要么为假,因此将始终选择前两种情况之一。
因此,如果这是优先于那些,则需要将其作为第一种情况:
CASE
WHEN A.Archived_Date IS NULL THEN DATEDIFF(DAY, Task_CreatedDate, GETDATE())
WHEN Isconverted = 'TRUE' THEN DATEDIFF(DAY, Task_CreatedDate, OPP.CreatedDate)
WHEN Isconverted = 'FALSE' THEN DATEDIFF(DAY, Task_CreatedDate, A.Archived_Date)
ELSE 'NULL'
END AS 'Age'
您可以通过将CASE
表达式放在DATEDIFF()
函数中来简化代码,您可以在其中使用COALESCE()
来检查A.Archived_Date
是否NULL
:
DATEDIFF(
DAY,
Task_CreatedDate,
CASE WHEN Isconverted = 'TRUE' THEN OPP.CreatedDate ELSE COALESCE(A.Archived_Date, GETDATE()) END
) AS Age