>*Table1 >*fields: > >Id ContrSum ContrDate >-- -------- --------- >1 100.00 2004/10/10 >2 200.00 2004/10/11 > >... > >*Table2 >*fields: > >Id PayedSum PayedDate >-- -------- --------- >1 50.00 2004/10/10 >1 20.00 2004/10/11 >1 10.00 2004/10/12 >1 20.00 2004/10/13 >2 200.00 2004/10/11 >>
> >Id ContrSum ContrDate PayedSum PayedDate >-- -------- ---------- -------- --------- >1 100.00 2004/10/10 50.00 2004/10/10 >1 0.00 20.00 2004/10/11 >1 0.00 10.00 2004/10/12 >1 0.00 20.00 2004/10/13 >2 200.00 2004/10/11 200.00 2004/10/11 > >>
create cursor Table1 ( Id I , ContrSum Y , ContrDate D ) insert into Table1 values(1, $100, {^2004/10/10}) insert into Table1 values(2, $200, {^2004/10/11}) insert into Table1 values(3, $300, {^2004/10/13}) insert into Table1 values(4, $300, {^2004/10/14}) Create cursor Table2 ( Id I , PayedSum Y , PayedDate D ) insert into Table2 Values(1, $50, {^2004/10/10}) insert into Table2 Values(1, $20, {^2004/10/13}) insert into Table2 Values(1, $10, {^2004/10/11}) insert into Table2 Values(1, $20, {^2004/10/12}) insert into Table2 Values(2, $100, {^2004/10/11}) insert into Table2 Values(2, $100, {^2004/10/13}) insert into Table2 Values(3, $200, {^2004/10/14}) SELECT T1.id ; , IIF(T2.PayedDate>MIN(T3.PayedDate),NULL,MAX(T1.ContrSum)) ContrSum; , IIF(T2.PayedDate>MIN(T3.PayedDate),NULL,MAX(T1.ContrDate)) ContrDate; , MAX(T2.PayedSum) PayedSum; , T2.PayedDate ; FROM Table1 T1 LEFT JOIN Table2 T2 ON T2.id=T1.id; LEFT JOIN Table2 T3 ON T3.id=T1.id; GROUP BY T1.id,T2.PayedDate * if you don't want payed contract SELECT T1.id ; , IIF(T2.PayedDate>MIN(T3.PayedDate),NULL,MAX(T1.ContrSum)) ContrSum; , IIF(T2.PayedDate>MIN(T3.PayedDate),NULL,MAX(T1.ContrDate)) ContrDate; , MAX(T2.PayedSum) PayedSum; , T2.PayedDate ; FROM Table1 T1 JOIN Table2 T2 ON T2.id=T1.id; LEFT JOIN Table2 T3 ON T3.id=T1.id; GROUP BY T1.id,T2.PayedDate