Page 1 of 1
Getting Most Recent Date
Posted: Wed Jul 12, 2006 12:00 pm
by 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.
Posted: Wed Jul 12, 2006 12:19 pm
by 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
Posted: Wed Jul 12, 2006 1:45 pm
by 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.
Posted: Wed Jul 12, 2006 2:04 pm
by kris007
Thanks Dsguru. I was thinking if there is any builtin function to do.
Posted: Wed Jul 12, 2006 4:40 pm
by 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
Posted: Wed Jul 12, 2006 5:48 pm
by kumar_s
You can acheive the same using sime If-Then-Else in Transformer.
Or Pivot and a aggregator/sorter.
Posted: Thu Jul 13, 2006 6:42 am
by 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.
Posted: Thu Jul 13, 2006 7:25 am
by rwierdsm
kris007 wrote:... 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
Posted: Thu Jul 13, 2006 7:29 am
by DSguru2B
Ouch, totally bypassed that. Need the column name huh?
Posted: Thu Jul 13, 2006 11:30 am
by 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
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.