Re: Date Operations in Stored Procedure

Can't you check for overlap with this simple statement?

assuming an existing date range: OrigStart through OrigEnd We want to test for overlap with the new date range: TestStart and TestEnd against OrigStart and OrigEnd

Overlap occurs if: TestEnd >= OrigStart and TestStart <= OrigEnd

Case 1: TestEnd and TestStart both prior to OrigStart: (no overlap) Result: FALSE

Case 2: TestEnd and TestStart both greater than OrigEnd: (no overlap)
Result: FALSE
Case 3: TestStart < OrigStart and TestEnd > OrigEnd: (overlap) Result: TRUE
Case 4: TestStart < OrigStart and, OrigStart < TestEnd < OrigEnd: (overlap)
Result: TRUE
Case 5: TestStart and TestEnd between OrigStart and OrigEnd: (overlap)
Result: TRUE
Case 6: OrigStart < TestStart < OrigEnd and TestEnd > OrigEnd (overlap)
Result: TRUE
Case 7: (boundary) TestStart = OrigStart and TestEnd=OrigEnd (complete

"Dirk Bellemans" <Dirk.Bellemans_at_skynet.belgium> wrote in message news:93ruhp$vlg$2_at_news0.skynet.be...
> I'm hesitant to do your homework, but I don't want you to follow bad
> examples either...
>
> I always use cursors. For the reasons why, check out the FAQ's or the
> manuals, but readability alone should do for now.
>
> At first look, there are 2 situations that indicate an overlap: (1) either
> of your date falls within the time period or (2) both dates fall outside
 the
> time period on the lesser (larger) side. If we'd be naive, we'd could it
> like that. However, Oracle will translate OR's into UNION's and we don't
> want that, because a UNION is not exactly what it looks like: A UNION B
 <=>
> A U (B\A) and the merge requires a sort operation! So, as a rule of thumb:
> NEVER user OR on large table scans (even through an index). Split the
 result
> up yourself into disjunct sets and code these with UNION ALL or in
 different
> cursors.
> Now, as for the return value. We don't want to count all the overlappig
> periods, we just want to check the existance of ONE. Unfortunately for the
> typist, the message "You have a problem", will not help him/her much to
> solve it. You should at least report something like "From date overlaps
> reservation number XXAZ, which runs from dd/mm/yyyy to dd/mm/yyyy, To date
> not checked".
>
> I did not include the package wrapping, you can figure that one out for
> yourself. I use a function that returns a number, but you still have to
 fill
> in the details itself.
>
> FUNCTION fChehkOverlap( pi_From IN DATE, pi_To IN DATE )
> RETURNS NUMBER
> IS
> CURSOR c_From ( ci_From IN DATE )
> IS
> SELECT /*+ INDEX(table iFromIndex) */
> pk
> FROM table
> WHERE StDate <= ci_From
> AND EndDate >= ci_From
> ;
> CURSOR c_To ( ci_To IN DATE )
> IS
> SELECT /*+ INDEX(table iToIndex) */
> pk
> FROM table
> WHERE StDate <= ci_To
> AND EndDate >= ci_To
> ;
> CURSOR c_Out ( ci_From IN DATE, ci_To IN DATE )
> IS
> SELECT /*+ INDEX(table iFromIndex) INDEX(table iToIndex)*/
> pk
> FROM table
> WHERE StDate > ci_From
> AND EndDate < ci_To
> ;
> d_dummy DATE;
> -- errorvalues
> BEGIN
> nReturn := NULL;
> -- From Date
> OPEN c_FROM (pi_From)
> FETCH c_From INTO d_dummy;
> IF c_FROM%FOUND THEN
> nReturn := nFromOverlap;
> END IF;
> CLOSE c_From; /* Some people would test if the cursor DID open, but
 I'd
> check for that error in the Exception section */
> IF nReturn IS NOT NULL THEN
> RETURN nReturn;
> END IF;
> -- You can figure the others yourself
> EXCEPTION
> WHEN OTHERS THEN
> RETURN nBadLuckBaby;
> END;
>
> dirkske.
>
> "Arun Solleti" <asolleti_at_grad.csee.usf.edu> wrote in message
> news:3A579914.9A793330_at_grad.csee.usf.edu...
> > What i have ?
> >
> > I table which contains reservation schdules for some given set of Items.
> > The Reservation schdules will be in the form of Data range i.e. start
> > date and end date. So i will have 3 field in the database Items, StDate,
> > EndDate. There can be multiple reservations for a single Item.
> >
> > Problems ?
> >
> > I am trying to write a stored operations which gets three input
> > parameters Item, D1, D2 for placing reservation on Item from D1 to D2.
> > For this purpose i have to parse through existing reservation scehdules
> > and verify if the i can reserve the Item between D1 and D2 or will it
> > overlap with existing schdules.
> >
> > Result ?
> >
> > My stored procedure should return OK if there is no overlap otherwise
> > RESERVED if there is a overlap.
> >
> >
>
>
Received on Fri Jan 19 2001 - 02:42:00 CET