How can I include null values in a MIN or MAX?

It's a bit ugly but because the NULLs 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