none
how to retrieve latest date column comparing with rest of the date columns ??

    Question

  • Hi Friends,

    I have 6 Date columns (some of col has Null data)  I need to compare all 6 columns date, how can I produce recent date to new Column! can you please help me Tsql Code or Function.

    Thanks for advance.



    • Edited by Cherukuri19 Friday, February 21, 2014 12:17 PM
    Friday, February 21, 2014 12:08 PM

Answers

  • Cherukuri,

    Check if this works for you..

    create table #datetest(id int,date1 datetime,date2 datetime,date3 datetime,date4 datetime,date5 datetime,date6 datetime)
    
    insert #datetest select 1,getdate(),getdate()-1,getdate()-2,getdate()-3,getdate()-4,getdate()-5
    insert #datetest select 2,getdate()+30,getdate()+30-1,getdate()+30-2,getdate()+30-3,getdate()+30-4,getdate()+30-5
    
    ;with cte
    as
    (
    	select *
    	from
    	(
    		select id,date1,date2,date3,date4,date5,date6
    		from #datetest
    	) tab
    	UNPIVOT
    	(
    		date for datenum in ([date1],[date2],[date3],[date4],[date5],[date6])
    	) upvt
    )
    select id,max(Date)
    from cte
    group by id


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Friday, February 21, 2014 2:12 PM

All replies

  • Hej 

    create table datetest(id int,date1 datetime,date2 datetime,date3 datetime,date4 datetime)


    insert into datetest values(1,GETDATE(),GETDATE()-1,GETDATE()-2,GETDATE()-3)


    go

    SELECT  MAX(date_columns) AS max_dateFROM    ( (SELECT   date1 AS date_columns FROM     datetest         )          UNION          ( SELECT  date2 AS date_columns            FROM    datetest          )          UNION          ( SELECT  date3 AS date_columns            FROM    datetest          )		  UNION          ( SELECT  date4 AS date_columns            FROM    datetest          )        ) AS date_query


    • Edited by kumaar1986 Friday, February 21, 2014 1:57 PM
    Friday, February 21, 2014 1:55 PM
  • Cherukuri,

    Check if this works for you..

    create table #datetest(id int,date1 datetime,date2 datetime,date3 datetime,date4 datetime,date5 datetime,date6 datetime)
    
    insert #datetest select 1,getdate(),getdate()-1,getdate()-2,getdate()-3,getdate()-4,getdate()-5
    insert #datetest select 2,getdate()+30,getdate()+30-1,getdate()+30-2,getdate()+30-3,getdate()+30-4,getdate()+30-5
    
    ;with cte
    as
    (
    	select *
    	from
    	(
    		select id,date1,date2,date3,date4,date5,date6
    		from #datetest
    	) tab
    	UNPIVOT
    	(
    		date for datenum in ([date1],[date2],[date3],[date4],[date5],[date6])
    	) upvt
    )
    select id,max(Date)
    from cte
    group by id


    Thanks,
    Jay
    <If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

    Friday, February 21, 2014 2:12 PM