Getting Most Recent Date

[kris007]

I have a requirement where I need to get the most recent date among all three different dates, the three different dates correspond to the 3 different date column in a DB2 Table.
Is there any function which gets the most recent date given three columns as input?
Thanks in Advance.

[DSguru2B]

Kris, do it in the sql itself

Code: Select all

select max(maxdate) as maxdate 
from 
(select date1 as maxdate
  from table 
union 
select date2 as maxdate
  from table 
union 
select date3 as maxdate
  from table 
) as Temp

[DSguru2B]

Actually Kris, i just looked at my code again.
If you want it at a per table basis then my code will work. But if you want the max date from three columns at the row level then you need a case statement. Use this

Code: Select all

select 
(case 
  when date1 >= date2 and date1 >= date3 then date1 
  when date2 >= date3 then date2 
  else date3 
end ) As MaxDate
from table

THe above code will give you max date at the row level, and the code in my earlier post will give you max date at the table level.

[kris007]

Thanks Dsguru. I was thinking if there is any builtin function to do.

[kris007]

I actually have 10 different date columns and also I need the column name which has the maximum date.The following technique gets too labourious for 10 date columns even doing it in transformer gets tedious.

Code: Select all

select 
(case 
  when date1 >= date2 and date1 >= date3 then date1 
  when date2 >= date3 then date2 
  else date3 
end ) As MaxDate 
from table 

.
Is there any other way to do that, I am thinking of pivot stage but not really sure if it serves the purpose. Need suggestions from dsgurus here.
Thanks

[kumar_s]

You can acheive the same using sime If-Then-Else in Transformer.
Or Pivot and a aggregator/sorter.

[DSguru2B]

Well, i dont think a function exists. In server there is Compare(), but that wont even work over here because the number of columns is more than 2.
As Kumar said, you can do this via if-then-else statements (lots of them for 10 columns).
I would go for the case statement in sql. I know its labourious, but i think its the cleanest.

[rwierdsm]

... and also I need the column name which has the maximum date...

Need to think about this one some more, the case statement will not provide the column name.

Kris, what are you going to do with the column name after you determine the max date?

Rob

[DSguru2B]

Ouch, totally bypassed that. Need the column name huh?

[kris007]

I didnot want to use if then else in the transformer as it is also as tedious as comparing in sql select stmt.
I was able to acheive this using

Code: Select all

XFM--->PIVOT--->SORT

in the transformer stage along with the date columns I am also outputting datecolumnnames as different columns.
Thanks guys for all your suggestions.You have been really helpful.