Archives of the TeradataForum

Message Posted: Thu, 03 Jul 2003 @ 08:32:11 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Bruce,

Try the following... I created a table that includes four date columns (dt1 to dt4) and column c1 which I used simply to compare the answer rows with my original data. I've also coded '3500-12-31' and '1900-01-01' as default values, you may want something different.

select t1.c1
      ,min(case
           when t1.dt1 is not null and t1.dt1 <
coalesce(t1.dt2,('3500-12-31' (date))) and t1.dt1 <
coalesce(t1.dt3,('3500-12-31' (date))) and t1.dt1 <
coalesce(t1.dt4,('3500-12-31' (date))) then t1.dt1
           when t1.dt2 is not null and t1.dt2 <
coalesce(t1.dt1,('3500-12-31' (date))) and t1.dt2 <
coalesce(t1.dt3,('3500-12-31' (date))) and t1.dt2 <
coalesce(t1.dt4,('3500-12-31' (date))) then t1.dt2
           when t1.dt3 is not null and t1.dt3 <
coalesce(t1.dt1,('3500-12-31' (date))) and t1.dt3 <
coalesce(t1.dt2,('3500-12-31' (date))) and t1.dt3 <
coalesce(t1.dt4,('3500-12-31' (date))) then t1.dt3
           when t1.dt4 is not null and t1.dt4 <
coalesce(t1.dt1,('3500-12-31' (date))) and t1.dt4 <
coalesce(t1.dt2,('3500-12-31' (date))) and t1.dt4 <
coalesce(t1.dt3,('3500-12-31' (date))) then t1.dt4
           else ('3500-12-31' (date))
           end) as mindate
      ,max(case
           when t2.dt1 is not null and t2.dt1 >
coalesce(t2.dt2,('1900-01-01' (date))) and t2.dt1 >
coalesce(t2.dt3,('1900-01-01' (date))) and t2.dt1 >
coalesce(t2.dt4,('1900-01-01' (date))) then t2.dt1
           when t2.dt2 is not null and t2.dt2 >
coalesce(t2.dt1,('1900-01-01' (date))) and t2.dt2 >
coalesce(t2.dt3,('1900-01-01' (date))) and t2.dt2 >
coalesce(t2.dt4,('1900-01-01' (date))) then t2.dt2
           when t2.dt3 is not null and t2.dt3 >
coalesce(t2.dt1,('1900-01-01' (date))) and t2.dt3 >
coalesce(t2.dt2,('1900-01-01' (date))) and t2.dt3 >
coalesce(t2.dt4,('1900-01-01' (date))) then t2.dt3
           when t2.dt4 is not null and t2.dt4 >
coalesce(t2.dt1,('1900-01-01' (date))) and t2.dt4 >
coalesce(t2.dt2,('1900-01-01' (date))) and t2.dt4 >
coalesce(t2.dt3,('1900-01-01' (date))) then t2.dt4
           else ('1900-01-01' (date))
           end) as maxdate
from bb1 t1
left join bb2 t2
on (t1.c1 = t2.c1)
group by 1
having mindate-maxdate < 365;

This seems to work ok, I'm confident that the logic is correct. I just hope you haven't got 15 date columns that need comparing !

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>