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
