In SQL Server, how do I get the latest dates with multiple columns of dates?

Question

This feels like it should be an easy one. How do I get the latest of 3 dates that are in different columns

DROP TABLE #dates
CREATE TABLE #dates (mykey CHAR(10), date1 DATETIME, date2 DATETIME, date3 DATETIME)
INSERT #dates VALUES ('Key1', '1/1/2015', '2/1/2015', '3/1/2105')
INSERT #dates VALUES ('Key2', '1/2/2015', '4/2/2015', '3/2/2105')
INSERT #dates VALUES ('Key3', '1/3/2016', '4/3/2015', '3/3/2105')

select mykey, ?? AS 'Latest Date' from #dates

I would like the result to be:

mykey       Latest Date
Key1        2105-03-01 00:00:00.000
Key2        2015-04-02 00:00:00.000
Key3        2016-01-03 00:00:00.000

You can easily achieve it using `MAX` from derived table based on `UNION ALL/VALUES` multiple columns — Lukasz Szozda, Dec 21 '15 at 21:43
I re-opened the question because I think the below answer is better than the referenced duplicate question. — Gordon Linoff, Dec 21 '15 at 21:43
date3 should be in 2015 as well, not in 2105 in order to get the results you wanted, otherwise it will return date3 for all of the keys — MazBros, Dec 21 '15 at 23:03
Ideally, fix your data model. All data of the same "type" (such that it makes sense that two values will be compared to each other) ought to be in a single column. When you have numbered columns, it's usually a sign that data has ended up embedded in metadata (i.e. 1, 2 and 3 are *meaningful*, but there's no way to write straightforward queries that use those numbers) or you have arbitrary restrictions (why does it stop at 3?) — Damien_The_Unbeliever, Dec 22 '15 at 06:59

Gordon Linoff · Answer 1 · 2015-12-21T22:21:48.763

8

Probably the easiest way in SQL Server is to use cross apply:

select d.*, m.maxdate
from #dates d cross apply
     (select max(dte) as maxdate
      from (values (date1), (date2), (date3)) as v(dte)
     )  m;

edited Dec 21 '15 at 22:21

answered Dec 21 '15 at 21:44

Gordon Linoff

1,242,037
58
646
786

1

even though it is good query it is not giving exact result as mentioned in output – mohan111 Dec 22 '15 at 08:15
@mohan111 . . . Presumably, the 2105 year is a typo in the sample data, based on the context of the question. Here is a SQL Fiddle: http://www.sqlfiddle.com/#!6/a52e6/2. – Gordon Linoff Dec 22 '15 at 12:28
No Exactly we need to get 2016 for key3 but In your query it is showing other value we need to MAX means highest date – mohan111 Dec 22 '15 at 12:40
@mohan111 . . . If I change the years in the third column, it returns 2016: http://www.sqlfiddle.com/#!6/09613/1. – Gordon Linoff Dec 22 '15 at 12:42

score 0 · Answer 2 · answered Dec 21 '15 at 22:37

Using a CASE statement would get the job done.

DECLARE @dates TABLE (mykey CHAR(10), date1 DATETIME, date2 DATETIME, date3 DATETIME)
INSERT @dates VALUES ('Key1', '1/1/2015', '2/1/2015', '3/1/2105')
INSERT @dates  VALUES ('Key2', '1/2/2015', '4/2/2015', '3/2/2105')
INSERT @dates  VALUES ('Key3', '1/3/2016', '4/3/2015', '3/3/2105')

select mykey, 
case when date1 >= date2 and date1 >= date3 THEN date1
    when date2 >= date1 and date2 >= date3 then date2
    else date3 end [LatestDate]
 from @dates

score 0 · Answer 3 · edited Dec 22 '15 at 06:59

0

Here we can use iif statement:

SELECT mykey
    ,iif(month(date1) > month(date2), iif(month(date1) > month(date3), date1, date3), iif(month(date2) > month(date3), date2, date3)) AS result
FROM #dates

edited Dec 22 '15 at 06:59

slavoo

5,798
64
37
39

answered Dec 22 '15 at 06:31

Chanukya

5,833
1
22
36

In SQL Server, how do I get the latest dates with multiple columns of dates?

3 Answers3