CODEWITHSUNDEEP
sqlsql-servercasedatediff
jackstraw22
jackstraw22

Reputation: 641

A better way to write a Case statement in DATEDIFF()?

I've written a query that finds the number of days a record has been in a particular status. I've got the query working correctly, but I just want to know if there's a more efficient way of writing this. My starting data looks like this:

   MemberID StatusDate1   StatusDate2   StatusDate3   StatusDate4
     77         2017-01-06    2017-03-30     NULL         2017-04-13
     88         NULL          NULL           2017-07-19   NULL

The end result looks like this:

   MemberID  StatusDate1   StatusDate2   StatusDate3   StatusDate4
     77         83             14            NULL          116
     88        NULL            NULL            19          NULL

The issue is if there's a NULL Status Date then I need to find the next populated date and use that. I'm using a Case statement for the calculation, and while it does work, I keep thinking there has to be a better way than how I've written it:

    Select *, DATEDIFF(dd, StatusDate1, case when StatusDate2 is not null then StatusDate2,
                                             when StatusDate2 is null then StatusDate3,
                                             when StatusDate3 is not null then StatusDate3,
                                             when StatusDate3 is null then StatusDate4,
                                             when StatusDate4 is null then getdate())end) as NewStatusDate1

I'm writing this for all 4 Status Dates. Is there a simpler way to write this?

Upvotes: 1

Views: 3026

Answers (3)

Wes H
Wes H

Reputation: 4439

Case will take the first true result. The way you had it, you would never get to StatusDate4 or GetDate because StatusDate2 would always return in one of the first two tests (NOT NULL or NULL).

You can simplify your case statement using this:

CASE
    WHEN StatusDate2 IS NOT NULL THEN StatusDate2
    WHEN StatusDate3 IS NOT NULL THEN StatusDate3
    WHEN StatusDate4 IS NOT NULL THEN StatusDate4
    ELSE GETDATE()
END

Upvotes: 0

Paul Holmes
Paul Holmes

Reputation: 162

As a side note, be aware that datediff measures 'boundary crossings', not the length of the interval.

Take these two points in time:

23:59 on Jan 1st

00:01 on Jan 2nd

In this scenario, the period is two minutes long.

For your needs, should that be considered a day?

Datediff will return 1 (when you use dd or equivalent), because the day part of the 2nd date is one more than the 1st.

If your data types are more precise than simply date, and instead you wanted it rounded - e.g longer than twelve hours is considered a day - you'll need a slightly different approach (e.g. Diff the minutes, then divide to get days)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I don't know if it is more efficient, but this is definitely easier to write and read:

SELECT *,
       DATEDIFF(day, StatusDate1, 
                COALESCE(StatusDate2, StatusDate3, StatusDate4, GETDATE()
               ) as NewStatusDate1

I'm not sure why you would call an integer results a "new status date". Are you sure that you don't want DATEADD()?

Upvotes: 1

Related Questions