Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Posts
    660

    Unanswered: check time range in store procedure.

    I have reservation database, suppose somebody reserved a resource on 10/12/2006 from 9:00am to 12pm. If anybody else want to reserve the same resource from 10am to 3pm. It will not let them reserver. I would like to check a range in store procedure. Is there has any function to check range in easy way?
    Many thanks.

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Check that first timepoint of second reservation attempt is not between
    starting and ending timepoint of the first reservation?

  3. #3
    Join Date
    Mar 2004
    Posts
    660
    Thank you! I have another question, in the front end, i would like to have a calendar form on it, when user click the date, it would be like 10/12/2006. Also i would like to have time dropdown box, like 8:00am, 9:00am....., How to put together to be the ScheduleDate (10/12/20068:00am), use string then convert to date? In the dropdown box, Is that the format should like 8:00am, or 8am?

    Thanks.

  4. #4
    Join Date
    Jul 2002
    Posts
    229
    I think you'll have to put the date and the time together
    in such a way that the resulting string can be converted
    (using convert or cast) to a datetime value.

  5. #5
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Quote Originally Posted by Coolberg
    Check that first timepoint of second reservation attempt is not between
    starting and ending timepoint of the first reservation?
    That's a start, but leaves some holes. What if the second reservation starts before the first but ends during or after? It would pass your test but still be a conflict.
    Paul

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    assume
    SD = start date of the range to be queried
    ED = start date of the range to be queried
    FD = from_date of a stored event
    TD = to_date of a stored event

    here are all the overlap possibilities:
    Code:
                SD        ED             
                 |         |               
    1   FD---TD  |         |               
                 |         |               
    2         FD-|-TD      |               
                 |         |               
    3            | FD---TD |               
                 |         |               
    4         FD-|---------|-TD       
                 |         |               
    5            |      FD-|-TD 
                 |         |               
    6            |         |  FD---TD
    you want to report all events except case 1 and case 6
    Code:
    ... where ED >= FD  /* eliminates case 6 */
          and SD <= TD  /* eliminates case 1 */
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Nice visual; helps people see all the possibilities. I use the same solution in my apps.
    Paul

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    to relate this to your example in post #1 ...

    stored reservation --
    FD = 2006-10-12 09:00
    TD = 2006-10-12 12:00

    requested reservation --
    SD = 2006-10-12 10:00
    ED = 2006-10-12 15:00

    you are looking at case #2

    the query will return a row

    when the query returns no rows, it means you can grant the request for a new reservation
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2009
    Posts
    3
    I'm facing the same issue and I tried to resolve it using the following codes in the table trigger (After Insert, Update).

    IF EXISTS (SELECT * FROM dbo.Appointment a INNER JOIN Inserted i
    ON a.AppointmentDate = i.AppointmentDate AND
    a.AppointmentNo <> i.AppointmentNo AND
    a.PhysicianNo = i.PhysicianNo AND
    a.StartTime < i.StartTime AND
    a.EndTime >= i.EndTime)

    BEGIN
    ROLLBACK
    RAISERROR('Physician is Busy at This Time', 16, 1)
    END

    But unfortunately it didn't work how I can change my trigger to make sure no appointment start and end times will overlaps with another appointment?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're not using the logic in post #6, are you

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you likely to insert many rows at once, or just one or a few at a time? If the latter I personally prefer to call a scalar function in a check constraint. I find the coding easier and it is arguably a little less obscure to others. On the other hand, it is less efficient than a trigger and would run very slowly if many rows are inserted at once.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2009
    Posts
    3

    appointment start and end times overlapping

    I'm having an appointments table and every appointment has a start and end time. At least 50 appointments will be inserted to the table daily.

    No appointment duplication allowed in terms of the date, start and end times for the same physician.

    I tried to satisfy this rule by creating the trigger but the overlapping between the appointments start and end times makes it hard for me. I could not control this situation.

  13. #13
    Join Date
    Jan 2009
    Posts
    3
    Thank you for your help I managed to resolve the problem.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •