Select max date with given condition

I am using SQL server 2012

create table t(dt1 date,dt2 date,dt3 date,dt4 date)

insert into t values('1970-01-01','2008-10-10',NULL,NULL),(NULL,'2008-10-10','2017-10-12',NULL),('1970-01-01','2008-10-10',NULL,'2018-10-09')

I need to get the minimum date from these columns, if the column value ='1970-01-01' then I need the second minimum date.

Below is what I tried which is not resulting correct result.

select *,case when (dt1='1970-01-01' or dt2='1970-01-01' or dt3='1970-01-01' or dt4='1970-01-01' )and dt1<=dt2  then dt1 else dt2
              end as DDt
from t

Expected output result:

enter image description here

Edit - I need the second minimum date, added more cases here.

enter image description here

4 answers

  • answered 2018-04-17 05:42 uzi

    Use outer apply

    select  * 
    from t
        outer apply (select ddt = min(v) 
                    from (values (dt1), (dt2), (dt3), (dt4)) q(v) 
                    where v > '19700101'
                ) q

  • answered 2018-04-17 05:45 Aswani Madhavan

    The following query works for the scenario : -

    SELECT *, MinValue 
    FROM t
    CROSS APPLY (SELECT MIN(d) MinValue FROM (VALUES (dt1), (dt2), (dt3),(dt4)) AS 
    a(d) WHERE d <> '01-01-1970') A

  • answered 2018-04-17 05:57 Yogesh Sharma

    You could use subquery with values constructors

    select *, (select min(dates) 
               from (values (dt1), (dt2), (dt3), (dt4))a(dates)
               where a.dates > '1970-01-01') as DDt
    from t;

  • answered 2018-04-17 06:23 Kaval Patel

    Try this solution :

        SELECT * , (SELECT MIN(Dates) FROM (VALUES (dt1), (dt2), (dt3), (dt4)) AS Fields(Dates) WHERE Fields.Dates > '1970-01-01')  AS DDT
    FROM [dbo].[t]