Finding max of 3 Max date columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding max of 3 Max date columns

Author

Topic

dave1816
Starting Member

9 Posts

Posted - 2014-05-13 : 11:13:26

Greetings,

I am struggling to work out how I would work out the max value of 3 max date values. Note the max date values may be null.

Here is my code if anyone can point me in the right direction it be much appreciated as I've been stuck on this for a while now

SELECT    TD.sro_num, SRO.description, SRO.cust_num, custad.name, SRO.sro_type, TD.whse, TD.CostCode, TD.stat_code, TD.[No of days in Status], 
          SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc, ((SRO.total_cost_lbr) + (SRO.total_cost_matl) + (SRO.total_cost_misc)) AS total,
		  convert(varchar,MAX(mt.trans_date), 103)as max_matl_transdate,  convert(varchar,MAX(lbr.trans_date), 103)as max_lbr_transdate, convert(varchar,MAX(misc.trans_date), 103) as max_misc_transdate
		
    	
FROM         TD_SroStatCount AS TD INNER JOIN 
             fs_sro AS SRO ON SRO.sro_num = TD.sro_num INNER JOIN
             custaddr AS custad ON custad.cust_num = SRO.cust_num  
             AND SRO.cust_seq = custad.cust_seq
	     left join fs_sro_matl mt on  SRO.sro_num = mt.sro_num  
             and mt.type ='A'
             left join  fs_sro_labor lbr  on  SRO.sro_num =
             lbr.sro_num and  lbr.type ='A'
	     left join  fs_sro_misc misc  on  SRO.sro_num = 
             misc.sro_num and misc.type ='A'

GROUP BY TD.sro_num, TD.whse, TD.CostCode, TD.[No of days in Status], TD.stat_code, SRO.description, SRO.cust_num, SRO.cust_seq, SRO.sro_type, 
custad.name, SRO.total_cost_lbr, SRO.total_cost_matl, SRO.total_cost_misc
ORDER BY TD.stat_code, TD.whse

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-13 : 11:16:42

Would you please post the DDL to create the table and some DML to populate it (INSERT INTO ...) with sample data? Also post what you are getting with your query and what you want to see.

Note that you can do something like this:


;with dates(date1, date2, date3) as (
	select * from (values
		(cast('2014-01-01' as datetime), cast('2014-01-02' as datetime), cast('2014-01-03' as datetime)),
		(cast('2015-01-01' as datetime), cast('2015-01-02' as datetime), cast('2015-01-03' as datetime)),
		(cast('2016-01-01' as datetime), cast('2016-01-02' as datetime), cast('2015-01-03' as datetime))
		) dates(date1, date2, date3)
	)
select max(date1), max(date2), max(date3) 
     , (select max(date1_3) from (values (max(date1)), (max(date2)), (max(date3)) )dates(date1_3)) maxof3
from dates

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-05-13 : 13:59:05

[code]
;with yourTable (rowid, date1, date2, date3)
as (
select 1, cast('2014-01-01' as datetime), cast('2014-01-02' as datetime), cast('2014-01-03' as datetime) union all
select 2, cast('2015-01-01' as datetime), cast('2015-01-02' as datetime), cast('2015-01-03' as datetime) union all
select 3, cast('2016-01-03' as datetime), cast('2016-01-02' as datetime), cast('2015-01-01' as datetime)
)

select date1, date2, date3, ca.maxDate
from yourTable yt
cross apply (
select max(dt) maxDate
from (
select yt.date1 union all
select yt.date2 union all
select yt.date3
) allDates (dt)
) ca

OUTPUT:

date1 date2 date3 maxDate
----------------------- ----------------------- ----------------------- -----------------------
2014-01-01 00:00:00.000 2014-01-02 00:00:00.000 2014-01-03 00:00:00.000 2014-01-03 00:00:00.000
2015-01-01 00:00:00.000 2015-01-02 00:00:00.000 2015-01-03 00:00:00.000 2015-01-03 00:00:00.000
2016-01-03 00:00:00.000 2016-01-02 00:00:00.000 2015-01-01 00:00:00.000 2016-01-03 00:00:00.000
[/code]

EDIT:

OR this for the overall max date
[code]
--select date1, date2, date3, ca.maxDate
select max(ca.maxDate) overallMax
[/code]
Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources