CODEWITHSUNDEEP
sqldateteradata
Stian
Stian

Reputation: 1391

Find the minimum date, but not null from different columns in a select

I have a Teradata SQL query with several different date fields which can be NULL. I want to find the earliest/lowest date, and return NULL only if all dates are NULL.

I tried the function LEAST, but that returns NULL as long as any date is NULL, as it is considered the lowest by this function.

My quick fix now is to COALESCE the dates to '9999-12-31' and then use a very ugly CASE statement to replace '9999-12-31' with NULL if that is the result of the LEAST function.

A minimal example of my code now:

CASE WHEN 
LEAST(
COALESCE(date1, date '9999-12-31')(INTEGER),
COALESCE(date2, date '9999-12-31')(INTEGER)
) = '9999-12-31' THEN NULL
ELSE
LEAST(
COALESCE(date1, date '9999-12-31')(INTEGER),
COALESCE(date2, date '9999-12-31')(INTEGER)
)(DATE) 
END

I would like it to be something like:

LEAST_WITHOUT_NULL(date1,date2)

And have it return the earliest date and NULL only in the case both dates are NULL.

I hope someone can suggest a better function, or even some better way to rewrite this.

Upvotes: 1

Views: 2711

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

For two dates, you can use this expression:

least(coalesce(date1, date2), coalesce(date2, date1))

This is easy to extend to three dates:

least(coalesce(date1, date2, date3),
      coalesce(date2, date1, date3),
      coalesce(date3, date2, date1))

Upvotes: 1

dnoeth
dnoeth

Reputation: 60462

You can only simplify the CASE to a NULLIF:

NULLIF(LEAST(COALESCE(date1, DATE '9999-12-31')(INTEGER),
             COALESCE(date2, DATE '9999-12-31')(INTEGER)), DATE '9999-12-31') (DATE)

Now it's mainly cut&paste for adding additional columns. Of course this would be easier if GREATEST/LEAST allowed DATEs and/or a kind of IGNORE NULLS option.

Upvotes: 2

Related Questions