Re: Scheduling Database - Design Help!

From: Tonkuma <tonkuma_at_jp.ibm.com>
Date: 19 Sep 2005 06:07:41 -0700
Message-ID: <1127135260.917783.11240_at_g47g2000cwa.googlegroups.com>

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')

Received on Mon Sep 19 2005 - 15:07:41 CEST