get a week range to record for each week?

[dave796]

Im trying to find unmarked registers from two existing tables.

Registers contains register_id, start_week, end_week so start_week could be 7 and end_week 36 this is all registers setup with unique register_id.

Then marked which conatins register_id, week signifying the register has been marked for that week number. All week numbers are based on academic year so are just integers.

So I thought the easiest way would be to query registers returning a record for each week in the start_week end_week range and then finding the ones which don't exist in marked. However I dont know how to create this recordset. Any help appreciated.

 

[rac2]

It can be difficult finding things which are not there.

Somehow you must create a set of rows for each week. This is independent and separate from Registers or Marked. One way to do this would be to create another table called Weeks. It could have a single column named WeekNumber with 53 rows and the integers from 1 to 53.

The unmarked weeks for a particular register_id would be

SELECT w.WeekNumber
FROM Weeks w
WHERE w.WeekNumber NOT IN (
         SELECT week FROM Marked
         WHERE register_id = 27121945
        )

This would list all of the weeks which were not marked for 27121945.

Hope I understood your question. Let me know.

 

[dave796]

Yeah you understood correctly thanks for the idea I just need to do a select on the weeks table between start_week and end_week as my registers don't run week 1 to 53 they usually run something like week 7 to 40 as its academic year timetables.

Anyway should be easy enough from here thanks.