Date booking percentages - Help required with logic

[zq29]

I'm trying to write a statistics page for a small booking system I am writing and need a bit of help with the logic (which will also be helpful when I'm scripting the front end to it).

My table has the following notable fields:
[0]start_date (date)
[0]start_slot (enum)
[0]end_date (date)
[0]end_slot (enum)

Each day is to have 3 slots, a morning (0), afternoon (1) and evening (2). I'm trying to work out the percentage of booked slots for each week of a selected year.

I have written the script that works out the date which each of the 52 weeks of the year start on, but I'm having troubles figuring out the logic behind working out how many slots have been booked when looping through the days of each week.

Any ideas?

Many thanks.

[Barand]

I don't understand the structure. Wouldn't just the date and slot be sufficient for each booking?

[corbin]

So basically what you want to do is:

Find the number of slots that are set for week X of year Y?

I don't understand what you're trying to do lol

[448191]

I'd say: find the number of taken slots on a specific day, for a specific slot.

Try:

[code]
SELECT COUNT(*) FROM Table WHERE
  start_date >= $date AND end_date <= $date #only count entries within date range
AND (
  IF($date = start_date, #if the date is a bookings start date,
      IF($slot >= start_slot, TRUE, FALSE), #...count entry if start_slot is equal or bigger than the one we're looking for
  IF($date = end_date, #if the date is a bookings end date,
      IF($slot <= end_slot, TRUE, FALSE), #...count entry if start_slot is equal or smaller than the one we're looking for
  TRUE) #otherwise just count it
)
[/code]