List:General Discussion« Previous MessageNext Message »
From:Gleb Paharenko Date:August 16 2005 11:01am
Subject:Re: Dates & Schedule Problems
View as plain text  
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" <nando@stripped> 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
 / /|_/ / // /\ \/ /_/ / /__   Gleb.Paharenko@stripped
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
       <___/   www.mysql.com



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