Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2005
    Location
    Auckland, New Zealand
    Posts
    11

    Unanswered: Comparing two date periods for overlapping

    hi guys,

    i have a booking table which has the following columns...

    booking
    -------------------------------------------
    dCheckin (format 11/9/2006 12:00:00 AM)
    dCheckout (format 11/11/2006 12:00:00 AM)

    when a new booking is entered, we want to make sure that the period entered does not conflict with an existing record.

    not sure how to go about building the query required. any help would be greatly appreciated.

    mike

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do periods that "touch" count as an overlap? Do you need to consider rooms, customers, or anything else for an overlap, or is all of the data in the table the same?

    -PatP

  3. #3
    Join Date
    Mar 2005
    Location
    Auckland, New Zealand
    Posts
    11
    it does matter if they are touching eg. someone cannot checkin during a period already occupied. the other data is in another table.

    m.

  4. #4
    Join Date
    Mar 2005
    Location
    Auckland, New Zealand
    Posts
    11
    ahh sorry patp. i see what you mean. touching yes it does matter. a person cannot checkin on the day someone checks out.

    mike

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Code:
    CREATE TABLE #patp (
       id			INT		IDENTITY
    ,  dCheckin		DATETIME
    ,  dCheckout		DATETIME
       )
    
    INSERT INTO #patp (
       dCheckin, dCheckout
       ) SELECT         '2006-01-01', '2006-01-10'
       UNION ALL SELECT '2006-01-15', '2006-01-20'
       UNION ALL SELECT '2006-02-01', '2006-02-10'
       UNION ALL SELECT '2006-02-10', '2006-02-15'
       UNION ALL SELECT '2006-03-01', '2006-03-10'
       UNION ALL SELECT '2006-03-02', '2006-03-07'
       UNION ALL SELECT '2006-04-01', '2006-04-10'
       UNION ALL SELECT '2006-04-08', '2006-04-13'
    
    SELECT *
       FROM #patp AS a
       WHERE EXISTS (SELECT *
          FROM #patp AS b
          WHERE b.id != a.id			-- Never compare a row with itself
             AND   (b.dCheckin <= a.dCheckout	-- A starts before B ends
                AND a.dCheckin <= b.dCheckout))	-- B ends after A starts
       ORDER BY a.dCheckin
    
    DROP TABLE #patp
    -PatP

Posting Permissions

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