Get 5 previous classes/class dates 1

[mts176]

I am not sure if I will explain this correctly but here goes.

Our website has a functionality that will allow certain users to schedule classes. We also have a feature that allows users to add people to these classes. On this particular page we have a query that gets all classes that have a start date >= todays date.

We are looking to show all classes that have a start date >= todays date plus the last 5 classes that occured before todays date. Is there anyway this can be done through a query or am I going to have to:
get all classes in the database for this particular course
put into an array
loop through array until today's date is found
go back the array index - 5

to get the last 5 classes?

Below is the query that I am using:

select l.class_id, l.class_date, l.class_size, to_char(l.start_time, 'hh12:mi AM') start_time, 
to_char(l.end_time, 'hh12:mi AM') end_time
from classes l, courses c 
where c.site_id = 1 
and l.course_id=c.course_id 
and l.course_id = 3736 
and l.start_time >= to_date('5/3/2006', 'mm/dd/yyyy') 
order by l.class_date

Thanks for the help

 

[Dagon]

Could you elaborate on what you mean by "start date >= todays date plus the last 5 classes that occured before todays date" ?

 

[mts176]

I am getting all classes that occur/start either today or sometime in the future. I want to get all of those classes plus the 5 previous classes no matter the date.

ex:
there are 2 classes either today or in the future
Start date Class Name
5/16/06 How to Use a Phone
5/20/06 How to Use a Phone

I want to get the last 5 instances of this class, those being:
Start Date Class Name
5/10/06 How to Use a Phone
5/08/06 How to Use a Phone
4/20/06 How to Use a Phone
4/18/06 How to Use a Phone
3/21/06 How to Use a Phone

The dates will not be in any kind of sequencial order and the class_ids will not be in any kind of order

I would like to know if there is a query I could write that could accomplish this for me.

Hope that helps

 

[Beilstwh]

select l.class_id, l.class_date, l.class_size, to_char(l.start_time, 'hh12:mi AM') start_time,
to_char(l.end_time, 'hh12:mi AM') end_time
from classes l, courses c
where c.site_id = 1
and l.course_id=c.course_id
and l.course_id = 3736
and l.start_time >= to_date('5/3/2006', 'mm/dd/yyyy')
union
select a.class_id, a.class_date, a.class_size, a.start_time, a.end_time
from
(select l.class_id, l.class_date, l.class_size, to_char(l.start_time, 'hh12:mi AM') start_time,
to_char(l.end_time, 'hh12:mi AM') end_time
from classes l, courses c
where c.site_id = 1
and l.course_id=c.course_id
and l.course_id = 3736
and l.start_time < to_date('5/3/2006', 'mm/dd/yyyy')
order by l.class_date desc) a
where rownum <= 5;


Bill
Oracle DBA/Developer
New York State, USA

 

[mts176]

Thank you very much Beilstwh, that is what I am looking for. It works like a charm.

 

[Beilstwh]

Glad to help and thanks for the star!!

Bill
Oracle DBA/Developer
New York State, USA