Create Dates out of nothing? (or something?)

[oXmike]

Hi Folks,
Any help would be appreciated as I'm really stumped.

Have just inherited a system that records absences from University Lectures for one of the departments. I am being tasked with outputting data that shows:

LEFT SIDE OF THE QUERY:
Every student & Every event (Done with cartesian...simple enough inc 'Event Date')

RIGHT SIDE OF THE QUERY:
Every student's attendance whether they attended a lecture or not.
Now this is where I get stuck. The reasons being:

1. Only absences have been recorded.
2. The 'Absences' table only has 'Date From' and 'Date To' fields. So I don't have a record for each and every lecture missed. Only a range of dates!

I therefore need to create a new date to add to the right hand side where a lecture date falls between the 'Date From' and 'Date To' fields that matches the 'Event Date' from the left hand side of the query.

For example:

Joe was off ill between 1st Sept 2010 and 10th Sept 2010 inclusive. Between those dates he should have attended lectures on the 1st, 2nd, 5th and 7th.
So on the left hand side I have 4 records for the events Joe should have attended with the 4 dates (1,2,5,7). But on the right (in the absences table) I only have the 'From' and 'To' dates.


How do I generate 4 records, each with one of the event dates for the right-hand side, just using the 'From' and 'To' dates?

I'm at a loss...

Thanks
Mike

 

[namliam]

You dont create the dates, you already have them

Simply put in the where:
[event date] between [absent start date] and [absent end date]

Since you inherited the db, I will forgo the usual lecture on Naming convententions and the use of spaces and special chars in (column) names as well as the one one not using reserved words (From)

P.S. Welcome to AWF

 

[oXmike]

Thanks for that, It has worked.

As for the inherited DB. It's a mess, believe me. But the real problem is that I'm not allowed to change anything yet, just work with what I have at least till I get my feet under the rug a bit more.

The person who designed it is my line manager, and I've only been here two weeks :-s

Battered and bemused, but grateful.

Mike.