Re: Need help with SQL Query

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.com> wrote in message news:<zrjQb.6133$U%5.35124_at_attbi_s03>...
> 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.ca> wrote in message
> news: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 Mon Jan 26 2004 - 20:20:27 CET