# Compare the 3 columns and pick up the latest date

### Question

• Hi,

I have a scenario  with 3 date columns , i want to compare 3 date columns  and pick the latest date from those 3.

I used case statement

(CASE WHEN (Date1 IS NULL OR Date2 IS NULL OR (Date3 > Date1  AND Date3 > Date2)) THEN Date3
WHEN (Date3 IS NULL OR Date2 IS NULL OR (Date1 > Date3 AND Date1 > Date2)) THEN Date1
WHEN (Date1 IS NULL OR Date3 IS NULL OR (Date2 > Date3 AND Date2 > Date1)) THEN Date2
ELSE NULL END )

Is there any function or any statement to get the result faster.

thanks,

aravind-

Friday, September 07, 2012 5:27 PM

• ```--create table tDates (id int identity(1,1), date1 datetime, date2 datetime, date3 datetime)
--insert into tDates values ('1/1/2012','1/2/2012','1/3/2012'), ('1/1/2012',null,'1/3/2012'), ('1/1/2012','1/2/2012',null)
SELECT id,
Max(date123) AS latestdate
FROM   (SELECT id,
date1,
date2,
date3
FROM   tDates) src
UNPIVOT (date123
FOR dates IN ([date1],
[date2],
[date3])) unpvt
GROUP  BY id ```

Friday, September 07, 2012 6:03 PM
• --SQL Server 2012 IIF function:

```create table tDates (id int identity(1,1), date1 datetime, date2 datetime, date3 datetime, myNum int, myNum2 int)
insert into tDates (date1,date2,date3,mynum) values ('1/1/2012','1/2/2012','1/3/2012', 1), ('1/1/2012',null,'1/13/2012',22), ('1/1/2012','1/2/2012',null,55), (null,null,null,55)
SELECT  NULLIF(IIF (date1>date2, IIF(date1>date3,date1,date3),IIF(date2>date3,date2,date3)), '1/1/1900') latestdate
FROM (
SELECT id,Isnull(date1, '1/1/1900') date1,
Isnull(date2, '1/1/1900') date2,
Isnull(date3, '1/1/1900') date3
FROM tDates) t
drop table tDates```

Friday, September 07, 2012 7:32 PM
• ```Declare @MyTable Table (ID Int Identity(1,1), Date1 Date, Date2 Date, Date3 Date);

Insert Into @MyTable
Values ('2012-09-12','2012-07-25','2012-06-06')
, ('2013-03-22','2012-06-03','2013-03-11')
, (NULL,NULL,NULL)
, ('2012-10-22','2012-12-30','2012-05-29')
, (NULL,'2012-08-07',NULL)
, ('2012-05-05','2012-08-03','2012-09-16')
, ('2012-11-18','2013-01-19','2012-11-20')
, ('2012-07-31','2012-06-26','2012-03-19')
, ('2012-12-09',NULL,'2013-01-28')
, ('2012-03-07','2012-12-21','2012-10-15')
, ('2013-02-27','2012-06-03','2013-02-13')
, ('2012-08-17','2012-09-14','2013-02-18')
, ('2013-03-07','2013-03-13','2012-12-09')
, ('2012-07-27','2012-06-19','2012-12-17')
, ('2012-03-08','2012-05-25','2012-03-07')
, ('2012-10-06','2012-03-31','2012-08-30')
, ('2012-06-22','2012-03-23','2012-12-02')
, ('2012-09-09','2012-03-18','2013-03-17')
, ('2012-06-30','2012-07-03','2013-01-30')
, ('2013-03-21','2012-04-07','2013-01-03')
, ('2013-02-24','2012-04-09','2013-01-23')
, ('2012-07-02','2012-06-18','2012-12-24')
, ('2012-12-25','2013-01-05','2012-11-16')
, ('2012-04-06','2012-12-24','2013-01-06')
, ('2012-05-29','2012-04-26','2012-07-28')

Select	ID, Date1, Date2, Date3, MaxDate
from	@MyTable A
Cross Apply
(	Select Max(MyDAte) As MaxDate
From (Values (Date1), (Date2), (Date3)) B(MyDate)
) C```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Sunday, September 09, 2012 11:32 PM

### All replies

• perhaps something like this:

```case when date1 >= date2
and date1 >= coalesce(date3, date1)
or date1 >= date3
and date1 >= coalesce(date2, date1)
then date1
when date2 >= date1
and date2 >= coalesce(date3, date2)
when date2 >= date3
and date2 >= coalesce(date1, date2)
then date2
when date3 >= date1
and date3 >= coalesce(date2, date3)
when date3 >= date2
and date3 >= coalesce(date1, date3)
then date3
end```

Well, when I got done with it I didn't like it, but here it is; maybe this will be of some help:

```declare @test table
(test_Id int, date1 date, date2 date, date3 date);
insert into @test
select 1, null, null, null union all
select 2, '20120101', null, null union all
select 3, null, '20120102', null union all
select 4, null, null, '20120103' union all
select 5, '20120101', '20120102', '20120103' union all
select 6, '20120101', '20120101', '20120101' union all
select 11, '20120101', '20120101', '20120102' union all
select 12, '20120101', '20120102', '20120101' union all
select 13, '20120102', '20120101', '20120101' union all
select 11, '20120101', '20120102', '20120102' union all
select 12, '20120102', '20120102', '20120101' union all
select 13, '20120102', '20120101', '20120102' union all
select 21, '20120101', '20120102', null union all
select 22, '20120101', null, '20120103' union all
select 23, '20120101', '20120103', null union all
select 24, '20120101', null, '20120102' union all
select 26, '20120102', '20120101', null union all
select 27, '20120102', null, '20120103' union all
select 28, '20120102', '20120103', null union all
select 29, '20120102', null, '20120101' union all
select 31, '20120103', '20120101', null union all
select 32, '20120103', null, '20120102' union all
select 33, '20120103', '20120102', null union all
select 34, '20120103', null, '20120101'
;
select *,
case when date1 >= date2
and date1 >= coalesce(date3, date1)
or date1 >= date3
and date1 >= coalesce(date2, date1)
or date1 is not null
and date2 is null and date3 is null
then date1
when date2 >= date1
and date2 >= coalesce(date3, date2)
or date2 >= date3
and date2 >= coalesce(date1, date2)
or date2 is not null
and date1 is null and date3 is null
then date2
when date3 >= date1
and date3 >= coalesce(date2, date3)
or date3 >= date2
and date3 >= coalesce(date1, date3)
or date3 is not null
and date1 is null and date2 is null
then date3
end
from @test;
/* -------- Output: --------
----------- ---------- ---------- ---------- ----------
1           NULL       NULL       NULL       NULL
2           2012-01-01 NULL       NULL       2012-01-01
3           NULL       2012-01-02 NULL       2012-01-02
4           NULL       NULL       2012-01-03 2012-01-03
5           2012-01-01 2012-01-02 2012-01-03 2012-01-03
6           2012-01-01 2012-01-01 2012-01-01 2012-01-01
11          2012-01-01 2012-01-01 2012-01-02 2012-01-02
12          2012-01-01 2012-01-02 2012-01-01 2012-01-02
13          2012-01-02 2012-01-01 2012-01-01 2012-01-02
11          2012-01-01 2012-01-02 2012-01-02 2012-01-02
12          2012-01-02 2012-01-02 2012-01-01 2012-01-02
13          2012-01-02 2012-01-01 2012-01-02 2012-01-02
21          2012-01-01 2012-01-02 NULL       2012-01-02
22          2012-01-01 NULL       2012-01-03 2012-01-03
23          2012-01-01 2012-01-03 NULL       2012-01-03
24          2012-01-01 NULL       2012-01-02 2012-01-02
26          2012-01-02 2012-01-01 NULL       2012-01-02
27          2012-01-02 NULL       2012-01-03 2012-01-03
28          2012-01-02 2012-01-03 NULL       2012-01-03
29          2012-01-02 NULL       2012-01-01 2012-01-02
31          2012-01-03 2012-01-01 NULL       2012-01-03
32          2012-01-03 NULL       2012-01-02 2012-01-03
33          2012-01-03 2012-01-02 NULL       2012-01-03
34          2012-01-03 NULL       2012-01-01 2012-01-03
*/```

Friday, September 07, 2012 5:46 PM
• ```--create table tDates (id int identity(1,1), date1 datetime, date2 datetime, date3 datetime)
--insert into tDates values ('1/1/2012','1/2/2012','1/3/2012'), ('1/1/2012',null,'1/3/2012'), ('1/1/2012','1/2/2012',null)
SELECT id,
Max(date123) AS latestdate
FROM   (SELECT id,
date1,
date2,
date3
FROM   tDates) src
UNPIVOT (date123
FOR dates IN ([date1],
[date2],
[date3])) unpvt
GROUP  BY id ```

Friday, September 07, 2012 6:03 PM
• I don't think the original query is correct.
Friday, September 07, 2012 6:04 PM
• The simplest way is to UNPIVOT these days into one column and then take max value out of it - as limno shown.

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

Friday, September 07, 2012 6:17 PM
• This looks a little less ragged, but I am still not sure if it is right:

```select
test_Id,
date1,
date2,
date3,
case when date12 >= coalesce(date3, date12) then date12 else date3
end as greatest_Date
from @test
cross apply
( select
case when date1 >= coalesce(date2, date1) then date1 else date2 end as date12
) as xa1
/* -------- Output: --------
test_Id     date1      date2      date3      greatest_Date
----------- ---------- ---------- ---------- -------------
1           NULL       NULL       NULL       NULL
2           2012-01-01 NULL       NULL       2012-01-01
3           NULL       2012-01-02 NULL       2012-01-02
4           NULL       NULL       2012-01-03 2012-01-03
5           2012-01-01 2012-01-02 2012-01-03 2012-01-03
6           2012-01-01 2012-01-01 2012-01-01 2012-01-01
11          2012-01-01 2012-01-01 2012-01-02 2012-01-02
11          2012-01-01 2012-01-02 2012-01-02 2012-01-02
12          2012-01-02 2012-01-02 2012-01-01 2012-01-02
12          2012-01-01 2012-01-02 2012-01-01 2012-01-02
13          2012-01-02 2012-01-01 2012-01-01 2012-01-02
13          2012-01-02 2012-01-01 2012-01-02 2012-01-02
21          2012-01-01 2012-01-02 NULL       2012-01-02
22          2012-01-01 NULL       2012-01-03 2012-01-03
23          2012-01-01 2012-01-03 NULL       2012-01-03
24          2012-01-01 NULL       2012-01-02 2012-01-02
26          2012-01-02 2012-01-01 NULL       2012-01-02
27          2012-01-02 NULL       2012-01-03 2012-01-03
28          2012-01-02 2012-01-03 NULL       2012-01-03
29          2012-01-02 NULL       2012-01-01 2012-01-02
31          2012-01-03 2012-01-01 NULL       2012-01-03
32          2012-01-03 NULL       2012-01-02 2012-01-03
33          2012-01-03 2012-01-02 NULL       2012-01-03
34          2012-01-03 NULL       2012-01-01 2012-01-03
*/```

( No, I've never seen it done this way; I just now cooked it up. )

Help please?  Would someone please check me and verify thumbs up or down whether or not this really works?

Friday, September 07, 2012 6:26 PM
• --SQL Server 2012 IIF function:

```create table tDates (id int identity(1,1), date1 datetime, date2 datetime, date3 datetime, myNum int, myNum2 int)
insert into tDates (date1,date2,date3,mynum) values ('1/1/2012','1/2/2012','1/3/2012', 1), ('1/1/2012',null,'1/13/2012',22), ('1/1/2012','1/2/2012',null,55), (null,null,null,55)
SELECT  NULLIF(IIF (date1>date2, IIF(date1>date3,date1,date3),IIF(date2>date3,date2,date3)), '1/1/1900') latestdate
FROM (
SELECT id,Isnull(date1, '1/1/1900') date1,
Isnull(date2, '1/1/1900') date2,
Isnull(date3, '1/1/1900') date3
FROM tDates) t
drop table tDates```

Friday, September 07, 2012 7:32 PM
• I have an article on sorting columns across a row waiting for publications. Two tricks: IFNULL() will convert a NULL to a real date and the lowest ISO-8601 date is '0001-01-01', so the greatest date in a pair is:

SET big_date
= (CASE WHEN IFNULL(date_1, '0001-01-01')
>= IFNULL(date_2, '0001-01-01')
THEN date_1 ELSE date_2 END);

You can then nest the expression inside itself to get three dates, or use a local variable,  but we could also use a BETWEEN

SET big_date
= CASE WHEN IFNULL(date_1, '0001-01-01')
BETWEEN IFNULL(date_2, '0001-01-01')
AND IFNULL(date_3, '0001-01-01')
THEN date_3
WHEN IFNULL(date_1, '0001-01-01')
BETWEEN IFNULL(date_3, '0001-01-01')
AND IFNULL(date_2, '0001-01-01')
THEN date_2
WHEN IFNULL(date_2, '0001-01-01')
BETWEEN IFNULL(date_1, '0001-01-01')
AND IFNULL(date_3, '0001-01-01')
THEN date_3
WHEN IFNULL(date_2, '0001-01-01')
BETWEEN IFNULL(date_3, '0001-01-01')
AND IFNULL(date_1, '0001-01-01')
THEN date_1
WHEN IFNULL(date_3, '0001-01-01')
BETWEEN IFNULL(date_2, '0001-01-01')
AND IFNULL(date_1, '0001-01-01')
THEN date_1
WHEN IFNULL(date_3, '0001-01-01')
BETWEEN IFNULL(date_1, '0001-01-01')
AND IFNULL(date_2, '0001-01-01')
THEN date_2
ELSE CAST(NULL AS DATE) END;

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Friday, September 07, 2012 10:31 PM
• Iimno is correct.  Your original query does not do what you want (pick the latest non-null date).  For example, if Date1 is NULL, Date2 is August 1, 2012, and Date3 is January 1, 2012, you want August 1, 2012 (Date2) returned.  But your first WHEN clause is

When ((Date1 IS NULL OR Date2 IS NULL OR (Date3 > Date1  AND Date3 > Date2)) Then Date3 which is evaluated as

When (TRUE OR FALSE OR (UNKNOWN AND FALSE)) Then Date3 which is

When (TRUE OR FALSE OR FALSE) Then Date3, so that When clause (and therefore the whole Case statement) will return Date3, that is January 1, 2012 which is not what you want.

Also, if what you are concerned about is performance, you don't have to worry much about calculations SQL must do on a row after SQL has determined that that row is included in the result (that is, calculations done only in the SELECT clause).  That's because these calculations are just CPU processing which is extremely fast compared to the disk accesses, matches, sorts, etc that are the expensive parts of most SQL queries.  So just do it in a straight-forward easy to maintain way.  What I would probably do is

```;With cte As
(Select <all the other columns you want>,
Case When Coalesce(Date1, Date2, Date3) Is Null Then 1 Else 0 End As AllDatesNull,
Coalesce(Date1, '00010101') As Date1,
Coalesce(Date2, '00010101') As Date2,
Coalesce(Date3, '00010101') As Date3
From <all the rest of your original query goes here)
Select <all the other columns you want>,
Case When AllDatesNull = 1 Then Null
When Date1 >= Date2 And Date1 >= Date3 Then Date1
When Date2 >= Date3 Then Date2
Else Date3 End
From cte;```
Tom

Friday, September 07, 2012 11:27 PM
• ```Declare @MyTable Table (ID Int Identity(1,1), Date1 Date, Date2 Date, Date3 Date);

Insert Into @MyTable
Values ('2012-09-12','2012-07-25','2012-06-06')
, ('2013-03-22','2012-06-03','2013-03-11')
, (NULL,NULL,NULL)
, ('2012-10-22','2012-12-30','2012-05-29')
, (NULL,'2012-08-07',NULL)
, ('2012-05-05','2012-08-03','2012-09-16')
, ('2012-11-18','2013-01-19','2012-11-20')
, ('2012-07-31','2012-06-26','2012-03-19')
, ('2012-12-09',NULL,'2013-01-28')
, ('2012-03-07','2012-12-21','2012-10-15')
, ('2013-02-27','2012-06-03','2013-02-13')
, ('2012-08-17','2012-09-14','2013-02-18')
, ('2013-03-07','2013-03-13','2012-12-09')
, ('2012-07-27','2012-06-19','2012-12-17')
, ('2012-03-08','2012-05-25','2012-03-07')
, ('2012-10-06','2012-03-31','2012-08-30')
, ('2012-06-22','2012-03-23','2012-12-02')
, ('2012-09-09','2012-03-18','2013-03-17')
, ('2012-06-30','2012-07-03','2013-01-30')
, ('2013-03-21','2012-04-07','2013-01-03')
, ('2013-02-24','2012-04-09','2013-01-23')
, ('2012-07-02','2012-06-18','2012-12-24')
, ('2012-12-25','2013-01-05','2012-11-16')
, ('2012-04-06','2012-12-24','2013-01-06')
, ('2012-05-29','2012-04-26','2012-07-28')

Select	ID, Date1, Date2, Date3, MaxDate
from	@MyTable A
Cross Apply
(	Select Max(MyDAte) As MaxDate
From (Values (Date1), (Date2), (Date3)) B(MyDate)
) C```

| Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

Hate to mislead others, if I'm wrong slap me. Thanks!

Sunday, September 09, 2012 11:32 PM
• Yet another IIF method if you have SQL Server 2012:

```select
test_Id,
iif(date12 >= coalesce(date3, date12), date12, date3) as greatest_Date
from @test
cross apply
( select iif(date1 >= coalesce(date2, date1), date1, date2) as date12
) as xa1;```

Monday, September 10, 2012 7:09 PM
• Hi Aravind, we recently started a blog series with the goal of teaching users various tips and tricks for solving a TSQL problem using different techniques.  I used this topic as the most recent post here:

http://blogs.msdn.com/b/samlester/archive/2012/09/10/tsql-solve-it-your-way-finding-the-max-value-from-multiple-columns.aspx

Thanks,
Sam Lester (MSFT)

My Blog

This posting is provided "AS IS" with no warranties, and confers no rights. Please remember to click "Mark as Answer" and "Vote as Helpful" on posts that help you. This can be beneficial to other community members reading the thread.

Tuesday, September 11, 2012 11:03 PM