none
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

Answers

  • --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


    View Steven Wang's profile on LinkedIn | 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


    View Steven Wang's profile on LinkedIn | 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