Re: Dates & Schedule Problems

[Peter Brawley]

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]