Hello.
I was able to make a query which retrieves dates where the TA is
available only for specific TA. To list all TA and periods of time
where they're available I wrote a stored procedure which works in
MySQL 5.
Here is the definition and test data in my tbl_notavailable
(I've changed some field names, like TAid to id, but they have
the same meaning):
*************************** 1. row ***************************
Table: tbl_notavailable
Create Table: CREATE TABLE `tbl_notavailable` (
`id` int(11) default NULL,
`startdate` datetime default NULL,
`enddate` datetime default NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8
id startdate enddate
1 2005-01-01 14:30:00 2005-01-01 15:23:00
1 2005-01-01 18:30:00 2005-01-02 09:23:00
1 2005-01-02 15:30:00 2005-01-02 16:03:00
1 2005-01-02 18:30:00 2005-01-03 09:03:00
2 2005-01-01 13:40:00 2005-01-01 14:15:00
2 2005-01-01 19:40:00 2005-01-02 10:15:00
2 2005-01-02 11:30:00 2005-01-02 12:01:00
2 2005-01-02 15:30:00 2005-01-02 16:21:00
2 2005-01-02 22:30:00 2005-01-03 10:21:00
And this is the results:
mysql> call p_avail();
+------+---------------------+---------------------+
| id | startdate | enddate |
+------+---------------------+---------------------+
| 1 | 2005-01-01 15:23:00 | 2005-01-01 18:30:00 |
| 1 | 2005-01-02 09:23:00 | 2005-01-02 15:30:00 |
| 1 | 2005-01-02 16:03:00 | 2005-01-02 18:30:00 |
| 2 | 2005-01-01 14:15:00 | 2005-01-01 19:40:00 |
| 2 | 2005-01-02 10:15:00 | 2005-01-02 11:30:00 |
| 2 | 2005-01-02 12:01:00 | 2005-01-02 15:30:00 |
| 2 | 2005-01-02 16:21:00 | 2005-01-02 22:30:00 |
+------+---------------------+---------------------+
I have a help table tbl_available with the same definition
as tbl_notavailable.
My stored procedure:
CREATE PROCEDURE p_avail()
DETERMINISTIC READS SQL DATA
BEGIN
DECLARE pID INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE pCOUNTER INT DEFAULT 0;
DECLARE pCOUNT INT DEFAULT 0;
DECLARE pTSTART DATE DEFAULT '0000-00-00';
DECLARE pTEND DATE DEFAULT '0000-00-00';
DECLARE cur1 CURSOR FOR SELECT DISTINCT id
FROM tbl_notavailable ORDER BY id;
DECLARE cur2 CURSOR FOR
SELECT t1.enddate AS TSTART
,(
SELECT t2.startdate AS T2END
FROM tbl_notavailable t2
WHERE t2.startdate > TSTART
AND id = pID
ORDER BY t2.startdate ASC LIMIT 1
) AS TEND
FROM tbl_notavailable t1
WHERE id = pID;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
TRUNCATE TABLE tbl_available;
SELECT COUNT(DISTINCT id) FROM tbl_notavailable INTO pCOUNT;
OPEN cur1;
SET pCOUNTER = 0;
loop1: LOOP
FETCH cur1 INTO pID;
SET done=0;
OPEN cur2;
hile1: WHILE done =0 DO
FETCH cur2 INTO pTSTART,pTEND;
IF (done = 1) THEN
LEAVE hile1;
END IF;
INSERT INTO tbl_available SET id = pID,
startdate = pTSTART, enddate = pTEND;
END WHILE hile1;
CLOSE cur2;
SET pCOUNTER = pCOUNTER+1;
IF( pCOUNTER = pCOUNT ) THEN
LEAVE loop1;
END IF;
END LOOP loop1;
CLOSE cur1;
DELETE FROM tbl_available WHERE enddate IS NULL;
SELECT * FROM tbl_available;
END
Here is the query which retrieves the available dates for specific id,
say, 2:
SELECT t1.enddate AS TSTART
,(
SELECT t2.startdate AS T2END
FROM tbl_notavailable t2
WHERE t2.startdate > TSTART
AND id = 2
ORDER BY t2.startdate ASC LIMIT 1
) AS TEND
FROM tbl_notavailable t1
WHERE id = 2;
"C.F. Scheidecker Antunes" <[EMAIL PROTECTED]> 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.
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
