Home » SQL & PL/SQL » SQL & PL/SQL » Checking Time in and out vs others (Oracle 10g)
Checking Time in and out vs others [message #331530] Thu, 03 July 2008 17:05 Go to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Basically what I Need to do is make sure when someone calls to schedule a time they say when they want to arrive and when they want to leave and which room they need And check that the dates and time does not conflict or overlap with a previous scheduled time..

I'm pretty much a newbie at pl/sql but this is what I came up with,
Any suggestions to improve or easier way?

DATE_LINES is a cursor and datetrig is going to be used later on if the date do conflict.

OPEN DATE_LINES;
    LOOP
      FETCH DATE_LINES INTO LINEROW;
      EXIT WHEN DATE_LINES%NOTFOUND;
        IF LINEROW.BOOK_ARRIVE_DATE > A_DATE AND LINEROW.BOOK_DEPART_DATE < D_DATE THEN
          DATETRIG := 'Y';

        ELSIF LINEROW.BOOK_ARRIVE_DATE > A_DATE AND LINEROW.BOOK_DEPART_DATE > D_DATE AND LINEROW.BOOK_ARRIVE_DATE < D_DATE  THEN
          DATETRIG := 'Y';

        ELSIF LINEROW.BOOK_ARRIVE_DATE < A_DATE AND LINEROW.BOOK_DEPART_DATE > D_DATE THEN
          DATETRIG := 'Y';

        ELSIF LINEROW.BOOK_ARRIVE_DATE < A_DATE AND LINEROW.BOOK_DEPART_DATE < D_DATE THEN
          DATETRIG := 'Y';
        ELSE
          END IF;
    END LOOP;
Re: Checking Time in and out vs others [message #331532 is a reply to message #331530] Thu, 03 July 2008 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Any suggestions to improve
It does appear that you tried to comply with Posting Guidelines, so no flames from me for now.
1) you did not explain what A_DATE or D_DATE are.
2) you did not explain what data is being returned by the cursor.
3) I suspect that the posted logic is seriously flawed, because it will almost always make the assignment DATETRIG := 'Y';
Any row which meets any of the 4 IF statement, will set the flag.

With free advice, you get what you paid for it & it may not be worth the electrons expended to compose it.
Re: Checking Time in and out vs others [message #331537 is a reply to message #331530] Thu, 03 July 2008 18:49 Go to previous messageGo to next message
psychoconker
Messages: 8
Registered: July 2008
Junior Member
Sorry bout not explaining enough.. The data that the cursor will be looking at is just 2 dates, the arrive date and depart date of previous scheduled appointments. A_DATE is the preferred arrival date of the new scheduling, and D_DATE is the preferred departure date.. its basically supposed to see if they conflict in anyway, the current booked schedule can't be changed as they were there first, so the only way that they wont conflict is if the new arrival date and departure date are either both before the old arrival date or both are after the old departure date.
Re: Checking Time in and out vs others [message #331565 is a reply to message #331537] Fri, 04 July 2008 00:50 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not what you check.
"so the only way that they wont conflict is if the new arrival date and departure date are either both before the old arrival date or both are after the old departure date. "

"ELSIF LINEROW.BOOK_ARRIVE_DATE < A_DATE AND LINEROW.BOOK_DEPART_DATE < D_DATE THEN"

You should check that linerow.book_depart_date > a_date.

also:
- remove the "else" (you have nothing to execute there)
- How many rows do you expect from your cursor? Telling from your explanation, I would say a single row. If that is true, don't do this in a loop; it might fool others to think that there will be more than one row.
- if there are multiple rows to be expected from the cursor, extend your exit-condition with "or datetrig = 'y'": after all, you don't need to check more rows after you found an overlapping.


The normal way to check overlap is:

new_startdate <= old_enddate and new_enddate >= old_startdate
Previous Topic: Problem with ELSE SQL%NOTFOUND THEN !!!
Next Topic: How to display the query result in procedure?
Goto Forum:
  


Current Time: Sat Dec 10 20:45:33 CST 2016

Total time taken to generate the page: 0.08967 seconds