Compare three Dates to get Latest 1

[timroop]

I have a table with three date fields. I need to return the latest (max) date of the three. I have a case statement that works fine unless a date is NULL at which point the function returns NULL. I need it to return the latest date of the three even if one or more is null. At any point either of the dates could be null but not all three.

Declare @Temp Table(EmpId VarChar(20), Date_1 smalldatetime, Date_2 smalldatetime, Date_3 smalldatetime)

Insert Into @Temp Values('01123DOE', '01/01/2001', '01/01/2002', '01/01/2003')
Insert Into @Temp Values('01123FOP', '01/01/2003', '01/01/2004', '01/01/2003')
Insert Into @Temp Values('01123JIM', '01/01/2000', '01/01/2002', '01/01/2000')
Insert Into @Temp Values('01123ACB', '01/01/2001', '01/01/2002', null)
Insert Into @Temp Values('01123FFF', '01/01/2001', '01/01/2002', '01/01/2003')

select EmpId, date_1, Date_2, Date_3,
case 
  when (date_1 >= date_2) AND (date_1 >= Date_3) then Date_1
  when (date_2 >= date_1) AND (date_2 >= Date_3) then Date_2
  when (date_3 >= date_1) AND (date_3 >= Date_2) then Date_3
end as MAX from @Temp

The above returns:

EmpID    Date_1     Date_2     Date_3     Max
01123DOE 2001-01-01 2002-01-01 2003-01-01 2003-01-01 
01123FOP 2003-01-01 2004-01-01 2003-01-01 2004-01-01 
01123JIM 2000-01-01 2002-01-01 2000-01-01 2002-01-01 
01123ACB 2001-01-01 2002-01-01 NULL       NULL
01123FFF 2001-01-01 2002-01-01 2003-01-01 2003-01-01

I could write in error traps for Null values but the case gets crazy.

Can anybody come up with a more elegant solution?

Thanks,

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[vongrunt]

How about:

select EmpID, max(date_N) from
(	select EmpID, Date_1 as Date_N from @temp union all
	select EmpID, Date_2 from @temp union all
	select EmpID, Date_3 from @temp
) blah
group by EmpID

(ANSI warnings included )?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[vongrunt]

That should work too (no ANSI gremlins behind)

select EmpID, case when Date_1_2 < Date_3 or Date_1_2 is null then Date_3 else Date_1_2 end as maxDate
from
(	select EmpID, 
		case when Date_1 < Date_2 or Date_1 is null then Date_2 else Date_1 end as Date_1_2, 
		Date_3
	from @Temp
) blah

Of course, with numbers of date_% columns growing this quickly becomes unmanageable... gotta love 0NF.

Maybe UNPIVOT if you are running SQL2005?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[timroop]

vongrunt:
Thanks. The code works fine but I'm muddling it up when I try to pleace it into my current select statement.

For simplicity sake let's say that I need to have it inserted as

select Empid, Date_1, Date_2, Date_3, MAX_DATE, Other_Fields
from {An inner join}

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[vongrunt]

In other words, source for EmpID and Date_1 - Date_3 columns is not a single table?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[timroop]

For the dates, yes it is a singe table, but I am inserting this into a select statement that pulls from other tables as well.

Tim

Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[vongrunt]

OK, then use this (myTable is source table for the dates):

select A.EmpID, A.Date_1, A.Date2, A.Date_3, B.maxDate
from myTable A
inner join 
(	select EmpID, max(date_N) as maxDate from
	(    select EmpID, Date_1 as Date_N from myTable union all
	    select EmpID, Date_2 from myTable union all
	    select EmpID, Date_3 from myTable
	) blah
	group by EmpID
) B on A.EmpID = B.EmpID

... and join it with other tables (on EmpID?) if necessary.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[timroop]

vongrunt
I never thanked you.

So...Thank you.

Tim


Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva