List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 14 2005 3:05pm
Subject:Re: Dates & Schedule Problems
View as plain text  
CF,

 >tbl_NotAvailable {
 >   TAid - int(11)
 >   StartDate  - DateTime
 >   EndDate - DataTime
 >}
<snip>
 >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?

You may be better off with an explicit calandar table containing slots 
for all possible timeslots. Absent that, you may be able to get your 
list from a query like the following, though you will probably have to 
adjust it for your context...

SELECT
  TAid,
  startdate AS beginavailable,
  enddate AS endavailable
FROM tbl_notavailable
WHERE TAid > 1 AND NOT EXISTS (
  SELECT enddate
  FROM tbl_notavailable
  WHERE startdate < beginavailable
    AND enddate >= endavailable
);

PB



C.F. Scheidecker Antunes wrote:

> 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.
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.338 / Virus Database: 267.10.8/71 - Release Date: 8/12/2005

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