Re: Scheduling Table
Date: Mon, 12 Jul 1999 15:58:36 GMT
Message-ID: <Mgoi3.13413$y92.7210_at_news.rdc1.ct.home.com>
You could probably do something like the following:
Procedure InsertEvent( nID IN NUMBER, dtStart IN DATE, nDur IN NUMBER, sResvd IN CHAR ) AS
CURSOR cFixed IS SELECT * FROM tableA WHERE reserved = 'N' AND start_time + (duration/24/60) > dtStart ORDER BY start_time);
rFixed cFixed%ROWTYPE;
dtNewStart DATE;
BEGIN
INSERT INTO tableA VALUES ( nID, dtStart, nDur, sResvd );
dtNewStart := dtStart + (nDur/24/60);
OPEN cFixed;
FETCH cFixed INTO rFixed;
- Loop through all moveable items after the one inserted
FOR rRec IN (SELECT * FROM tableA WHERE reserved = 'Y' AND start_time +
(duration/24/60) > dtStart ORDER BY start_time) LOOP
- Find next fixed item this record can inserted before WHILE dtNewStart + (rRec.duration/24/60) > rFixed.start_time AND cFixed%FOUND LOOP dtNewStart := rFixed.start_time + (rFixed.duration/24/60); FETCH cFixed INTO rFixed; END LOOP; UPDATE tableA SET start_time = dtNewStart WHERE id = rRec.id; dtNewStart := rRec.start_time + (rRec.duration/24/60); END LOOP; CLOSE cFixed; END;
AJ
Glenn Griffith <gagriff_at_bellsouth.net> wrote in message
news:3789F4B8.9C817B84_at_bellsouth.net...
> I'm looking for help with a scheduling table. I have a table which is
> used to schedule events. It consists of the following columns:
>
> ID NUMBER, -- unique identifier
> Start_Time DATE, -- Date and time the event starts
> Duration NUMBER, -- How long in minutes does the event last
> Reserved CHAR -- Y/N column determines if the event can be moved
>
> I want to write a PL/SQL procedure which will insert a new event into
> the table. Any events that occur after the new event must be pushed and
> given new times. The insert cannot push any events which are marked
> Reserved='N'. All non-reserved evenst (Reserved='Y') must be pushed
> around the Reserved events. Any ideas?
>
Received on Mon Jul 12 1999 - 17:58:36 CEST