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@y ahoo.ca
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@y ahoo.ca