Another approach Sorry I am not able to test these
Select * from ( select a.student_id, a.course_id, b.course_name, a.course_date, b.course_fee,effective_date,max(effective_date) over (partition by b.course_id) mx from student a inner join couse b on a.course_id=b.course_id where effective_date<=(select max(effective_date) from course where effective_date <=a.course_date) where effective_date =mx On Nov 17, 2012 1:48 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> wrote: > Select * from > (select a.student_id, a.course_id, b.course_name, a.course_date, > b.course_fee,effective_date,max(effective_date) over (partition by > b.course_id) mx > from student a inner join couse b on a.course_id=b.course_id and > effective_date<=a.course_date) > > where effective_date =mx > On Nov 17, 2012 1:18 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> > wrote: > >> Sorry. This is wrong >> On Nov 17, 2012 1:10 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> >> wrote: >> >>> Updated version >>> >>> select a.student_id, a.course_id, b.course_name, a.course_date, >>> b.course_fee from student a inner join couse on a. Course_id where >>> a.course_Date <=(select max(effective_Date) from course where >>> effective_date <= a.course_date) >>> On Nov 17, 2012 1:01 PM, "Gopakumar Pandarikkal" <pandarik...@gmail.com> >>> wrote: >>> >>>> select a.student_id, a.course_id, b.course_name, a.course_date, >>>> b.course_fee from student a inner join on Course b where a.course_id = >>>> b.course_id where a.course_Date = (select max(effective_Date) from course >>>> where effective_date <= a.course_date) >>>> >>>> Try this tell me if its ok >>>> On Nov 16, 2012 6:22 AM, "SQL-Help" <sketin...@gmail.com> wrote: >>>> >>>>> I have a STUDENT table that has >>>>> Student_id >>>>> Student_name >>>>> Course_id >>>>> Course_date >>>>> >>>>> So my records look like >>>>> >>>>> 123 Kathy Smith A103 12/01/2011 >>>>> 123 Kathy Smith A102 12/01/2011 >>>>> 124 Chris Jones A103 06/01/2011 >>>>> 124 Chris Jones A102 06/01/2011 >>>>> >>>>> The I have another table COURSE >>>>> Course_id >>>>> Course_name >>>>> Course_fee >>>>> Effective_date >>>>> >>>>> A103 Accounting1 200.00 04/01/2011 >>>>> A103 Accounting1 210.00 06/01/2011 >>>>> A103 Accounting1 220.00 10/01/2011 >>>>> A102 Writing101 150.00 04/01/2011 >>>>> >>>>> >>>>> what I need is >>>>> >>>>> Student_id, Student_name, Course_id, Course_name, course_date and >>>>> Course_fee >>>>> >>>>> (based on the date a particular student signed up for the course, >>>>> course fee should be determined) >>>>> >>>>> so for >>>>> >>>>> 123 Kathy smith A103 Accounting1 12/01/2011 220.00 >>>>> 124 Chris Jones A103 Accounting1 06/01/2011 210.00 >>>>> 123 Kathy Smith A102 Writing102 12/01/2011 150.00 >>>>> 124 Chris Jones A102 Writing 102 04/01/2011 150.00 >>>>> >>>>> >>>>> >>>>> This is how I tried and it doesn't work >>>>> >>>>> select a.student_id, a.course_id, b.course_name, a.course_date, >>>>> b.course_fee >>>>> from student a left outer join on Course b where a.course_id = >>>>> b.course_id >>>>> and b.effective_Date = (select effective_Date from course where >>>>> effective_date <= a.course_date) >>>>> >>>>> This is not for my school homework..working as a junior developer (so >>>>> I created a scenario similar to my issue at work and am asking for >>>>> help). >>>>> Thank you >>>>> >>>>> -- >>>>> You received this message because you are subscribed to the Google >>>>> Groups "Oracle PL/SQL" group. >>>>> To post to this group, send email to Oracle-PLSQL@googlegroups.com >>>>> To unsubscribe from this group, send email to >>>>> oracle-plsql-unsubscr...@googlegroups.com >>>>> For more options, visit this group at >>>>> http://groups.google.com/group/Oracle-PLSQL?hl=en >>>>> >>>> -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
