IF/ELSE query for date results

Database

IF/ELSE query for date results

Let's say I have these results from a subquery. They are are grouping by date with the MAX() of a unique identifier [xxxxx]. Assume the number appended to the unique identifer describes the highest number. This means the 05/08/13 unique identifer is the greatest.

05/05/13 xxxx1

05/07/13xxxx2

05/08/13 xxxx3

I have a table with the following two dates. I expect the results in bold italic

05/06/13 xxxx3

05/07/13 xxxx2

What I'm asking to occur is when there is a date match, provide me the associated unique identifer. If there is no match on date, provide me the greatest unique identifer.

How can I do this with a query?

1 REPLY
Teradata Employee

Re: IF/ELSE query for date results


create table A (

dt date,

v varchar(5)

);

insert into A values ('2013-05-05','xxxx1');

insert into A values ('2013-05-07','xxxx2');

insert into A values ('2013-05-08','xxxx3');

create table B(

dt date

);

insert into B values ('2013-05-06');

insert into B values ('2013-05-07');

select B.dt,

             case when A.dt is not null then A.v else A_max.v end as v 

from B

join (select max(v) as v from A) as A_max ON 1=1 

left join A ON B.dt = A.dt

order by 1