Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest
and least
..
least(extendDate,min), greatest(extendDate,max)
When I try to use these though, I get
'least' is not a recognized built-in function name.
'greatest' is not a recognized built-in function name.
That would cover extension in either direction.
For the purposes of the question, you would still have to do exclusive range replacement.
I'm just wondering how SQL Server users implement query patterns to mimic least
and greatest
functionality.
- PostgreSQL
GREATEST
/LEAST
- MySQL
GREATEST
/LEAST
- MariaDB
GREATEST
LEAST
- DB2
GREATEST
LEAST
- Oracle
GREATEST
LEAST
Do you unroll the conditions into CASE
statements or is there an extension, third party add-on, or license from Microsoft that enables this functionality?
Best Answer
One common method is to use the
VALUES
clause, andCROSS APPLY
the two columns aliased as a single column, then get theMIN
andMAX
of each.There are other ways of writing it, for example using
UNION ALL
However, the resulting query plans seem to be the same.