CODEWITHSUNDEEP
sqlsql-servert-sql
Ant Swift
Ant Swift

Reputation: 21177

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

I have a table where I am storing timespan data. the table has a schema similar to:

ID INT NOT NULL IDENTITY(1,1)   
RecordID INT NOT NULL  
StartDate DATE NOT NULL  
EndDate DATE NULL

And I am trying to work out the start and end dates for each record id, so the minimum StartDate and maximum EndDate. StartDate is not nullable so I don't need to worry about this but I need the MAX(EndDate) to signify that this is currently a running timespan.

It is important that I maintain the NULL value of the EndDate and treat this as the maximum value.

The most simple attempt (below) doesn't work highlighting the problem that MIN and MAX will ignore NULLS (source: http://technet.microsoft.com/en-us/library/ms179916.aspx).

SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid

I have created an SQL Fiddle with the basic setup done.

http://sqlfiddle.com/#!3/b0a75

How can I bend SQL Server 2008 to my will to produce the following result from the data given in the SQLFiddle?

RecordId  Start       End  
1         2009-06-19  NULL
2         2012-05-06  NULL
3         2013-01-25  NULL
4         2004-05-06  2009-12-01

Upvotes: 99

Views: 185260

Answers (7)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

Upvotes: 28

SHO
SHO

Reputation: 1

I try to use a union to combine two queries to format the returns you want:

SELECT recordid, startdate, enddate FROM tmp Where enddate is null UNION SELECT recordid, MIN(startdate), MAX(enddate) FROM tmp GROUP BY recordid

But I have no idea if the Union would have great impact on the performance

Upvotes: -1

TheBakker
TheBakker

Reputation: 3042

Use the analytic function :

select case when 
    max(field) keep (dense_rank first order by datfin desc nulls first) is null then 1 
    else 0 end as flag 
from MYTABLE;

Upvotes: 0

Matthew Erwin
Matthew Erwin

Reputation: 1214

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.

Upvotes: 68

a1ex07
a1ex07

Reputation: 37354

Assuming you have only one record with null in EndDate column for a given RecordID, something like this should give you desired output :

WITH cte1 AS
(
SELECT recordid, MIN(startdate) as min_start , MAX(enddate) as max_end
FROM tmp 
GROUP BY recordid
)

SELECT a.recordid, a.min_start , 
CASE 
   WHEN b.recordid IS  NULL THEN a.max_end
END as max_end
FROM cte1 a
LEFT JOIN tmp b ON (b.recordid = a.recordid AND b.enddate IS NULL)

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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).

Upvotes: 107

Marko Juvančič
Marko Juvančič

Reputation: 5890

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

Upvotes: 8

Related Questions