simplify a SQL case statement in a case expression

Question

How would I simplify this case statement in T-SQL? It provides the desired result, but it's very unwieldy and hard to read. I have to use the inner case statement to convert a Julian date (aka 6 digit number) into a regular date format.

Basically i'm doing a datediff( getdate(), case statement). Getdate() just returns the time now (ie. 2/27/2020) and the case statement converts a julian date (ie. 123456) into a normal date (ie, 1/1/2020).

Here's the expect output if the query was ran today on Feb 27.

Select CASE 
        WHEN Datediff(day, Getdate(), CASE 
                    WHEN a.wadpl = 0
                        THEN NULL
                    ELSE Dateadd(d, Substring(Cast(wadpl AS VARCHAR(6)), 4, 3) - 1, CONVERT(DATETIME, CASE 
                                    WHEN LEFT(Cast(wadpl AS VARCHAR(6)), 1) = '1'
                                        THEN '20'
                                    ELSE '21'
                                    END + Substring(Cast(wadpl AS VARCHAR(6)), 2, 2) + '-01-01'))
                    END) < 0
            THEN 'Overdue Now'
        WHEN Datediff(day, Getdate(), CASE 
                    WHEN a.wadpl = 0
                        THEN NULL
                    ELSE Dateadd(d, Substring(Cast(wadpl AS VARCHAR(6)), 4, 3) - 1, CONVERT(DATETIME, CASE 
                                    WHEN LEFT(Cast(wadpl AS VARCHAR(6)), 1) = '1'
                                        THEN '20'
                                    ELSE '21'
                                    END + Substring(Cast(wadpl AS VARCHAR(6)), 2, 2) + '-01-01'))
                    END) <= 30
            THEN 'Coming due in 01-30 days'
        ELSE 'Not Overdue'
        END [Overdue Status]
FROM Table_X

Answer 1

Here is a easy one to understand, assuming a.wadpl is an integer:

SELECT CASE 
  WHEN DATEDIFF(DAY, GETDATE(), DATEADD(DAY, a.wadpl % 1000, DATEADD(YEAR,a.wadpl / 1000,'1899-12-31'))) <0 THEN 'Overdue now'
  WHEN DATEDIFF(DAY, GETDATE(), DATEADD(DAY, a.wadpl % 1000, DATEADD(YEAR,a.wadpl / 1000,'1899-12-31'))) <= 30 THEN 'Coming due in 01-30 days'
  ELSE 'Not Overdue'
  END [Overdue Status]
FROM Table_X

or you can simplify by using a subquery (or you can use a WITH):

SELECT CASE 
  WHEN Age <0 THEN 'Overdue now'
  WHEN Age <= 30 THEN 'Coming due in 01-30 days'
  ELSE 'Not Overdue'
  END [Overdue Status]
FROM (
  SELECT DATEDIFF(DAY,GETDATE(),
    DATEADD(DAY,wadpl%1000,DATEADD(YEAR,wadpl/1000,'1899-12-31'))) Age, * 
  FROM Table_X) a

This will of course cause you to do this arithmetic for each row, and you can't easily use any indexes. If you were asking about aggregates, then I would suggest doing the opposite, and pre-calculating the dates and use those in your query instead. You might also want to consider putting a persisted computed column on table_x:

ALTER TABLE TABLE_X
ADD wadpl_dt AS
  (DATEADD(DAY,wadpl%1000,DATEADD(YEAR,wadpl/1000,'1899-12-31'))) PERSISTED;

Now you can just refer to table_x.wadpl_dt whenever you want the datetime, and your query would become:

SELECT CASE 
  WHEN Age <0 THEN 'Overdue now'
  WHEN Age <= 30 THEN 'Coming due in 01-30 days'
  ELSE 'Not Overdue'
  END [Overdue Status]
FROM (
  SELECT DATEDIFF(DAY,GETDATE(), wadpl_dt) Age, * 
  FROM Table_X) a

Here is the easy way to convert a date to what you refer to as the julian date:

SELECT (DATEPART(YEAR,GETDATE())-1900) * 1000 + DATEPART(DAYOFYEAR, GETDATE())

And this is how you can use it:

DECLARE @overdue int;
DECLARE @next30 int;

SET @overdue = (SELECT (DATEPART(YEAR,GETDATE())-1900) * 1000 + DATEPART(DAYOFYEAR, GETDATE()));
SET @next30 = (SELECT (DATEPART(YEAR,GETDATE()+30)-1900) * 1000 + DATEPART(DAYOFYEAR, GETDATE()+30));
SELECT CASE 
  WHEN wadpl < @overdue THEN 'Overdue now'
  WHEN wadpl <= @next30 THEN 'Coming due in 01-30 days'
  ELSE 'Not Overdue'
  END [Overdue Status]
FROM Table_X