how to find earliest date from null and non null dates

Analytics
Enthusiast

how to find earliest date from null and non null dates

Problem - There are 4 dates for each customer. One or more of these dates can be null. We need to find earliest non null date from among these. I tried LEAST function but that just puts null date as the earliest date. Iused this code but that won't work.,

 

 ,CAST(LEAST
         (           
           CAST(COALESCE(DT1, DATE '1900-01-01') AS INT),
           CAST(COALESCE(DT2, DATE '1900-01-01') AS INT),
           CAST(COALESCE(DT3, DATE '1900-01-01') AS INT),
           CAST(COALESCE(DT4 '1900-01-01') AS INT)
          )AS DATE) AS leastdate

Thanks in advance


Accepted Solutions
Supporter

Re: how to find earliest date from null and non null dates

Hi Dnoeth,

 

I miss-understood the requirement. I got it now.

 

Thanks,
Rohan Sawant

1 ACCEPTED SOLUTION
6 REPLIES
Junior Contributor

Re: how to find earliest date from null and non null dates

Come on, that's too easy :-)

 

Use a high date instead of 1900:

COALESCE(DT1, DATE '9999-12-31')

And if all columns might be NULL you need to add a final NULLIF:

 ,NULLIF(CAST(LEAST
         (           
           CAST(COALESCE(DT1, DATE '9999-12-31') AS INT),
           CAST(COALESCE(DT2, DATE '9999-12-31') AS INT),
           CAST(COALESCE(DT3, DATE '9999-12-31') AS INT),
           CAST(COALESCE(DT4, DATE '9999-12-31') AS INT)
          )AS DATE), DATE '9999-12-31') AS leastdate

 

 

 

Supporter

Re: how to find earliest date from null and non null dates

Hi dnoeth ,

 

May be I am missing something but I don't understand how changing the date will change the outcome. How can NULL be a outcome with coalesce applied at all cases. Can you please help me in understanding it.

 

Thanks,

Rohan Sawant

Junior Contributor

Re: how to find earliest date from null and non null dates

When all input columns are NULL the LEAST will return '9999-12-31', but the actual result should be NULL.

Teradata Employee

Re: how to find earliest date from null and non null dates

You want the least of 4 dates, but you don't want Null to show up as the least.  Therefore you have to treat Null as it were the highest date.  That's what this part does:

 

LEAST (
    CAST(COALESCE(DT1, DATE '9999-12-31') AS INT),
    CAST(COALESCE(DT2, DATE '9999-12-31') AS INT),
    CAST(COALESCE(DT3, DATE '9999-12-31') AS INT),
    CAST(COALESCE(DT4, DATE '9999-12-31') AS INT) )

 

But if all the dates are Null, you probably Do want Null as the result.  That's what this does:

 

NULLIF( CAST( Least( ... ) AS DATE), DATE '9999-12-31')

 

In other words, if the result of the Least() function is 9999-12-31, then this returns Null.  That's how you know all 4 dates were null.

Supporter

Re: how to find earliest date from null and non null dates

Hi Dnoeth,

 

I miss-understood the requirement. I got it now.

 

Thanks,
Rohan Sawant

Enthusiast

Re: how to find earliest date from null and non null dates

Thanks dnoeth! That was indeed too easy, thanks for the solution.