Board index » delphi » Appointments Table Checking For Conflicting Appointments

Appointments Table Checking For Conflicting Appointments

Hi
    I have a table that store appointments the stucture is:

CREATE TABLE "APPOINTMENT"
(
  "APPOINTMENTID" INTEGER NOT NULL,
  "PATIENTID" INTEGER,
  "PROBLEMID" INTEGER,
  "DATEAP" DATE,
  "TIMEAP" TIME,
  "DURATIONMINUTES" INTEGER,
CONSTRAINT "PK_APPOINTMENT" PRIMARY KEY ("APPOINTMENTID")
);

What i need when a new appointment is added to the table is check to make
sure that the appointent does not conflict with anyother appointment (ie
there are no over lapping appointments). If the appointment does conflict
with another then the new appointment will be rejected. I need to use a
trigger for this but i am not sure how to check to see there is a
conflicting appointment. Can anybody help?

Thankyou

Charlie

 

Re:Appointments Table Checking For Conflicting Appointments


In article <eb364a.h6r...@server1.thegrosvenors>,
char...@thegrosvenors.fsnet.co.uk says...

Quote

> What i need when a new appointment is added to the table is check to make
> sure that the appointent does not conflict with anyother appointment (ie
> there are no over lapping appointments). If the appointment does conflict
> with another then the new appointment will be rejected. I need to use a
> trigger for this but i am not sure how to check to see there is a
> conflicting appointment. Can anybody help?

CREATE TRIGGER ...
  AS
BEGIN
  IF (EXISTS
        (SELECT
           APPOINTMENTID
         FROM
           APPOINTMENT
         WHERE
           [...])) THEN BEGIN
    EXCEPTION ...
  END
END

        HTH,

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Re:Appointments Table Checking For Conflicting Appointments


Thankyou for the reply. Each appointment in the table has a date that the
appointment is on which is stored in the 'DATEAP' field and a time that the
appointment is on that day which is stored in the 'TIMEAP' field. There is
also a field called 'DURATIONMINUTES' which stores the how long the
appointment is in minutes. When i say conflicting appointments i mean make
sure that no appointments overlap. So i would need to when going though each
record in the table increment the TIMEAP field by the number of minutes
'DURATIONMINUTES' then check to see if the new time is between those values
and if so raise the exception. I also have the problem of say there was an
appointment at 11:30pm and went on till 00:30 the next day. Does anybody
have an ideas about how i could do this?

Thankyou

Charlie

Quote
"Craig Stuntz" <cstuntz@no_spam.vertexsoftware.com> wrote in message

news:MPG.16d1b27c1e46368c9898c1@newsgroups.borland.com...
Quote
> In article <eb364a.h6r...@server1.thegrosvenors>,
> char...@thegrosvenors.fsnet.co.uk says...

> > What i need when a new appointment is added to the table is check to
make
> > sure that the appointent does not conflict with anyother appointment (ie
> > there are no over lapping appointments). If the appointment does
conflict
> > with another then the new appointment will be rejected. I need to use a
> > trigger for this but i am not sure how to check to see there is a
> > conflicting appointment. Can anybody help?

> CREATE TRIGGER ...
>   AS
> BEGIN
>   IF (EXISTS
>         (SELECT
>            APPOINTMENTID
>          FROM
>            APPOINTMENT
>          WHERE
>            [...])) THEN BEGIN
>     EXCEPTION ...
>   END
> END

> HTH,

> -Craig

> --
>  Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
>      Delphi/InterBase WebLog: http://delphi.weblogs.com
>      InterBase PLANalyzer (Free IB optimization tool):
>           http://delphi.weblogs.com/IBPLANalyzer

Re:Appointments Table Checking For Conflicting Appointments


In article <8q794a.i58...@server1.thegrosvenors>,
char...@thegrosvenors.fsnet.co.uk says...

Quote
> Thankyou for the reply.

        I see you've also posted in the .sql newsgroup.  That's the best
place for this conversation, so I'll answer you there.

        -Craig

--
 Craig Stuntz (TeamB) Vertex Systems Corp. Columbus, OH
     Delphi/InterBase WebLog: http://delphi.weblogs.com
     InterBase PLANalyzer (Free IB optimization tool):
          http://delphi.weblogs.com/IBPLANalyzer

Other Threads