I think this is do-able, and possibly even simple, but it's been too
many years since I've done anything significant with SQL. Any help
would be appreciated.

I have two tables, PAY and CHG. PAY contains payments to employees
and the dates each payment was made. CHG contains dates employees
were hired and to what group they belonged. CHG also contains any
changes (updates) to which group they belong along with the effective
date of such change. I'd like to join the two tables in such a way
that each payment is associated with the group the employee was a
member of at the time the payment was made to them.

PAY.EmpNo PAY.PostDate PAY.Amt
========= ============ =======
1010 10-JAN-04 2163
1010 17-JAN-04 2645
1010 24-JAN-04 2313
1010 31-JAN-04 2354
1011 10-JAN-04 2321
1011 17-JAN-04 2211
1011 24-JAN-04 2242
1011 31-JAN-04 2211
1012 17-JAN-04 2433
1012 24-JAN-04 2246
1012 31-JAN-04 2235
1013 17-JAN-04 2766
1013 24-JAN-04 2661
1013 31-JAN-04 2627

CHG.EmpNo CHG.Act CHG.Grp CHG.EffDate
========= ======= ======= ===========
1010 New AAAA 12-FEB-01
1011 New CCCC 11-NOV-02
1011 Upd BBBB 18-JAN-04
1012 New EEEE 11-JAN-04
1013 New DDDD 11-JAN-04
1013 Upd BBBB 18-JAN-04
1013 Upd AAAA 25-JAN-04


desired Resulting Table:
RES.EmpNo RES.PostDate RES.Amt RES.Grp
========= ============ ======= =======
1010 10-JAN-04 2163 AAAA
1010 17-JAN-04 2645 AAAA
1010 24-JAN-04 2313 AAAA
1010 31-JAN-04 2354 AAAA
1011 10-JAN-04 2321 CCCC
1011 17-JAN-04 2211 CCCC
1011 24-JAN-04 2242 BBBB
1011 31-JAN-04 2211 BBBB
1012 17-JAN-04 2433 EEEE
1012 24-JAN-04 2246 EEEE
1012 31-JAN-04 2235 EEEE
1013 17-JAN-04 2766 DDDD
1013 24-JAN-04 2661 BBBB
1013 31-JAN-04 2627 AAAA

====================
Philip Hachey
philip_hachey (AT) yahoo (DOT) ca

Hello Philip,

Given:

create table Pay (EmpNo int, PostDate date, Amt int);

insert into pay values(1010, '10-JAN-04', 2163);
insert into pay values(1010, '17-JAN-04', 2645);
insert into pay values(1010, '24-JAN-04', 2313);
insert into pay values(1010, '31-JAN-04', 2354);
insert into pay values(1011, '10-JAN-04', 2321);
insert into pay values(1011, '17-JAN-04', 2211);
insert into pay values(1011, '24-JAN-04', 2242);
insert into pay values(1011, '31-JAN-04', 2211);
insert into pay values(1012, '17-JAN-04', 2433);
insert into pay values(1012, '24-JAN-04', 2246);
insert into pay values(1012, '31-JAN-04', 2235);
insert into pay values(1013, '17-JAN-04', 2766);
insert into pay values(1013, '24-JAN-04', 2661);
insert into pay values(1013, 31-JAN-04', 2627);

create table Chg(EmpNo int, Act varchar(10), Grp varchar(10), EffDate
date);

insert into Chg values(1010, 'New', 'AAAA', '12-FEB-01');
insert into Chg values(1011, 'New', 'CCCC', '11-NOV-02');
insert into Chg values(1011, 'Upd', 'BBBB', '18-JAN-04');
insert into Chg values(1012, 'New', 'EEEE', '11-JAN-04');
insert into Chg values(1013, 'New', 'DDDD', '11-JAN-04');
insert into Chg values(1013, 'Upd', 'BBBB', '18-JAN-04');
insert into Chg values(1013, 'Upd', 'AAAA', '25-JAN-04');

One way would be:

select pay.empno, pay.postdate, pay.amt, chg.grp
from pay, chg
where pay.empno=chg.empno
and (pay.empno, postdate, effdate) in (select pay.empno, postdate,
max(effdate) from
pay, chg
where pay.empno=chg.empno and postdate >=
effdate
group by pay.empno, postdate)


.... and another, faster method:


select empno, postdate, amt, grp from
(select pay.empno, pay.postdate, pay.amt, chg.grp,
row_number() over (partition by pay.empno, postdate order by effdate
desc) rn
from pay, chg
where pay.empno=chg.empno and postdate >= effdate)
where rn = 1


EMPNO POSTDATE AMT GRP
1010 1/10/2004 2163 AAAA
1010 1/17/2004 2645 AAAA
1010 1/24/2004 2313 AAAA
1010 1/31/2004 2354 AAAA
1011 1/10/2004 2321 CCCC
1011 1/17/2004 2211 CCCC
1011 1/24/2004 2242 BBBB
1011 1/31/2004 2211 BBBB
1012 1/17/2004 2433 EEEE
1012 1/24/2004 2246 EEEE
1012 1/31/2004 2235 EEEE
1013 1/17/2004 2766 DDDD
1013 1/24/2004 2661 BBBB
1013 1/31/2004 2627 AAAA

Rgds.

VC

"Philip Hachey" <philip_hachey (AT) yahoo (DOT) ca> wrote

Thanks, VC! After posting, I did manage to figure out a working query
very similar to your first one, but admittedly rather convoluted.
Yours is much more elegant. I could not use your second query, as my
DB doesn't seem to support those commands (such as the row_number()
function).

Thanks for the reply!
Philip

"VC" <boston103 (AT) hotmail (DOT) com> wrote