Need help with SQL Query
From: Philip Hachey <philip_hachey_at_yahoo.ca>
Date: 23 Jan 2004 12:05:38 -0800
Message-ID: <f2b1ca3d.0401231205.356c976e_at_posting.google.com>
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.
Philip Hachey
philip_hachey_at_yahoo.ca Received on Fri Jan 23 2004 - 21:05:38 CET
Date: 23 Jan 2004 12:05:38 -0800
Message-ID: <f2b1ca3d.0401231205.356c976e_at_posting.google.com>
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.ca Received on Fri Jan 23 2004 - 21:05:38 CET