Case Statement with DateDiff

Transact-SQL
1

his is what I have. I know that he datediff work independent. I am looking at how long a support ticket has been open and group by category.

Case When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE()))>7
THEN '7+ Days'
When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) >8 AND DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) < 12
THEN '8 - 12 Days Old'
When DATEDIFF(D,Defect.SubmittedDate,ISNULL(Defect.FixedDate,GETDATE())) >12
else '12+ Days old'
end

2

what is the issue ??

please let us know

:slight_smile:
:slight_smile:

3

A CASE statement stops at the first match. Thus, you have to be very careful about the order of the WHEN conditions.

Also, for readability and ease of maintenance, use a CROSS APPLY to assign an alias name to the calc so you don't have to repeat it in every WHEN:

Something like this:

SELECT ...,
    CASE WHEN days_open >= 12 THEN '12+ Days Old'
         WHEN days_open BETWEEN 8 AND 11 AND THEN '8 - 11 Days Old'
         ELSE '7 Days Or Less Old'
    END AS Days_Old_Text
FROM dbo.table_name
CROSS APPLY (
    SELECT DATEDIFF(D,Defect.SubmittedDate,
        ISNULL(Defect.FixedDate,GETDATE())) AS days_open
) AS alias1