How can I include null values in a MIN or MAX?
It's a bit ugly but because the NULL
s have a special meaning to you, this is the cleanest way I can think to do it:
SELECT recordid, MIN(startdate),
CASE WHEN MAX(CASE WHEN enddate IS NULL THEN 1 ELSE 0 END) = 0
THEN MAX(enddate)
END
FROM tmp GROUP BY recordid
That is, if any row has a NULL
, we want to force that to be the answer. Only if no rows contain a NULL
should we return the MIN
(or MAX
).
Use IsNull
SELECT recordid, MIN(startdate), MAX(IsNull(enddate, Getdate()))
FROM tmp
GROUP BY recordid
I've modified MIN in the second instruction to MAX
The effect you want is to treat the NULL as the largest possible date then replace it with NULL again upon completion:
SELECT RecordId, MIN(StartDate), NULLIF(MAX(COALESCE(EndDate,'9999-12-31')),'9999-12-31')
FROM tmp GROUP BY RecordId
Per your fiddle this will return the exact results you specify under all conditions.
In my expression, count(enddate)
counts how many rows where the enddate
column is not null.
The count(*)
expression counts total rows.
By comparing, you can easily tell if any value in the enddate
column contains null
. If they are identical, then max(enddate)
is the result. Otherwise the case
will default to returning null
which is also the answer. This is a very popular way to do this exact check.
SELECT recordid,
MIN(startdate),
case when count(enddate) = count(*) then max(enddate) end
FROM tmp
GROUP BY recordid