XPost: comp.database.oracle
From: boston103@hotmail.com
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" wrote in message
news:f2b1ca3d.0401231205.356c976e@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@yahoo.ca
--- SoupGate-Win32 v1.05
* Origin: you cannot sedate... all the things you hate (1:229/2)
|
