Subtracting from a CASE result

Question

I have the following CASE statement that returns the Closure Date column and want to subtract the Created On from the Closure Date column to get the number of days difference. I can do this with DATEDIFF but unsure how to make it it's own column using the existing CASE argument. I've tried a second CASE statement, even using a subquery, to no avail.

SELECT
    createdon [Created On],
    modifiedon [Modified On],
    timestamp [Time Stamp],
    CASE
        WHEN DATEDIFF(minute,0, [Created On]) = DATEDIFF(minute,0,[Modified On]) THEN [Modified On]
        ELSE [Time Stamp]
    END AS 'Closure Date',
    FROM
        Table;

Current results:

Created On	Closure Date
7/14/23 21:23	7/14/23 13:45
7/14/23 21:12	7/14/23 12:45
7/14/23 21:11	7/14/23 12:49

What I am looking for:

Created On	Closure Date	Days
7/13/23 21:27	7/14/23 21:48	1.014618
7/14/23 9:24	7/14/23 21:45	0.514421
7/14/23 13:53	7/14/23 21:45	0.327616

You have to repeat the whole expression (not statement) or wrap in up in a cte/derived table/cross apply. — shawnt00, Jul 14 '23 at 22:24

score 0 · Accepted Answer · answered Jul 14 '23 at 23:42

Your current comparison in the case when checks whether created and modified is within the same minute. If created = 13:30:59 and modified = 13:31:01 then your comparison will result in FALSE. Is this the behavior you expect?

About your question: I didn't validate it, but this should solve your problem.

Select 
[Created On]
, Case
    When DATEDIFF(minute,0, [Created On]) = DATEDIFF(minute,0,[Modified On])
    Then [Modified On]
    Else [Time Stamp]
End as [Closure Date]
, Case
    When DATEDIFF(minute,0, [Created On]) = DATEDIFF(minute,0,[Modified On])
    Then Cast([Modified On] - [Created On] as float)
    Else Cast([Time Stamp] - [Created On] as float)
End as [Days]
From Table

Please note, that I used floats, which can be imprecise. When you need exact numbers please read about decimales.

Subtracting from a CASE result

1 Answers1