95

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
Cœur
  • 37,241
  • 25
  • 195
  • 267
Ant Swift
  • 20,089
  • 10
  • 38
  • 55

7 Answers7

103

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • 1
    +1 . . . But the OP really wants the maximum end date, despite the SQL ("so the minimum StartDate and maximum EndDate"). – Gordon Linoff Jan 22 '14 at 14:59
  • 1
    @GordonLinoff I actually want to maintain the NULL as the highest value. I have edited the question to clarify, thanks. – Ant Swift Jan 22 '14 at 15:12
60

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.

Matthew Erwin
  • 1,116
  • 11
  • 7
  • 8
    This is cleaner than the accepted answer IMO. I use a slight variation: `NULLIF(MAX(COALESCE(EndDate, 'infinity'::timestamp)), 'infinity'::timestamp)` – jbg Feb 19 '17 at 14:18
  • No idea what dbms that's for, but the question was for SQL server. – Echilon Feb 27 '17 at 10:32
  • 26
    Note that in 8,000 years we'll need to review this code... but yeah, a lot cleaner than the accepted answer. – Devin Lamothe Mar 07 '17 at 00:49
  • I believe @jbg's variation is PostgreSQL specific – J.Warren Sep 15 '20 at 10:53
  • Anyone looking for smallest date (i.e. start_date or similar), the smallest supported date in MySQL is `1000-01-01` [Source](https://dev.mysql.com/doc/refman/8.0/en/datetime.html) – Advena Feb 10 '22 at 08:24
28

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
Kirby
  • 15,127
  • 10
  • 89
  • 104
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
  • Passed my tests, including the new multiple nulls for a group even if this should never happen. http://sqlfiddle.com/#!3/2c45b/2 – Ant Swift Jan 23 '14 at 08:52
7

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

t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92
Marko Juvančič
  • 5,792
  • 1
  • 25
  • 41
  • 10
    or even better, **Coalesce**, which is ansi-sql compatible – TPAKTOPA Jan 22 '14 at 14:56
  • 2
    Unfortunately I need to maintain the null as the high value. I've edited my question to be clearer on this. – Ant Swift Jan 22 '14 at 15:09
  • the question is asking for max/min value of enddate. I fail to see how getdate can replace the lowest or the highest value in an unknown table it could be a calender table. I also fail to see the advanage of returning getdate instead of null. – t-clausen.dk Jan 22 '14 at 15:19
  • The sample was incorrect, stating MIN rather than MAX for the EndDate column. – Ant Swift Jan 22 '14 at 15:26
1

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)
a1ex07
  • 36,826
  • 12
  • 90
  • 103
  • This does in fact work. It would be interesting to look if there was any performance difference over Damien_The_Unbeliever's answer. – Ant Swift Jan 22 '14 at 15:34
  • Not really a great answer. As you mention it require only 1 row with null column and the other answers are shorter and doesn't have prerequisites – t-clausen.dk Jan 22 '14 at 17:44
0

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;
TheBakker
  • 2,852
  • 2
  • 28
  • 49
-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

SHO
  • 1
  • 2