Posted by Tonkuma on 09/19/05 16:07

Sorry, this was not tested. Only my idea.

CREATE TABLE Appointment
(id SMALLINT NOT NULL
,astart TIMESTAMP NOT NULL
,afinish TIMESTAMP NOT NULL
,person VARCHAR(20) NOT NULL
,telno VARCHAR(12) NOT NULL
)


SELECT b.afinish AS available_start
, TIME('00:00:00') + DEC(n.astart-b.afinish, 6,0) AS
available_duration
FROM Appointment b
, TABLE
(SELECT MIN(n.astart)
FROM Appointment n
WHERE n.astart > b.afinish
) n(astart)
WHERE TIME(:requested_duration) - TIME('00:00:00') >= (n.astart -
b.afinish)
-- * Find the first available appointment in September
-- AND MONTH(b.afinish) = 9
-- AND MONTH(n.astart) = 9
--
-- * Find the first afternoon appointment
-- AND b.afinish >= TIMESTAMP(CURRENT DATE, '12:00:00')
-- AND n.astart <= TIMESTAMP(CURRENT DATE, '23:59:00')

[Back to original message]