List:General Discussion« Previous MessageNext Message »
From:C.F. Scheidecker Antunes Date:August 14 2005 7:03am
Subject:Dates & Schedule Problems
View as plain text  
Hello all,

I have a little table o TAs (teach assistants) with the following MySQL 
schema:

tbl_NotAvailable {
    TAid - int(11)
    StartDate  - DateTime
    EndDate - DataTime
}

This table logs the times where TAs are NOT available. So If a TA is not 
available on Monday from 12:00 to Tuesday 13:30 there will
be an entry on his table like (001,2005-08-15 12:00,2005-8-16 13:30) 
where 001 is the TA Id.

Question one:
Now, how can I return a calendar of the dates where the TA is AVAILABLE, 
that is the oposite of what is recorded?
I want a list of all the available days and times by substracting the 
non available times recorded in the table.
I guess I would need to produce a SELECT statement of all the days from 
Start to End and exclude those days that
are recorded on the table
What I want is given an interval say 2005-08-01 00:00:00 to 2005-08-31 
23:59:00, how can I get a list of all days where there is
no activity based on the records when the TA is not available?

Question two:
I want to make sure a you can book a time on the table that does not 
conflict with an existent one. How can I do it?

I've been browsing Joe Celko's book for ideas.

Any thoughts on how to accomplish this?

Thanks in advance,

C.F.
Thread
Dates & Schedule ProblemsC.F. Scheidecker Antunes14 Aug
  • Re: Dates & Schedule ProblemsJasper Bryant-Greene14 Aug
  • Re: Dates & Schedule ProblemsPeter Brawley14 Aug
  • Re: Dates & Schedule ProblemsGleb Paharenko16 Aug