Compare data of one table with another based on date & Time

Question

I have to compare data between two table and check for any conflicts.

Below is sample data for two table Events & Bookings I need to compare bookings Table with Events Table based on Date & Time & show records which are in conflict.

Actually user can change event date & time once date is change user need to generate report that will show them if they already had any booking for that particular date & time-slot.

I am using following query to check if we have any booking for the updated events

Query

Declare @bTime varchar(10) = '10:30';
Declare @bDate date = '2012-08-15';
SELECT * FROM  EventDatesReporting   WHERE 
((CONVERT(time(0), EventStartTime) <= CONVERT(time(0),@bTime )  
    AND CONVERT(time(0), EventEndTime) >= CONVERT(time(0),@bTime ))
OR 
(CONVERT(time(0), EventStartTime) <= DATEADD(minute,59,CONVERT(time(0), @bTime))   
    AND CONVERT(time(0), EventEndTime) >= DATEADD(minute,59,CONVERT(time(0), @bTime))))
AND EventDates = @bDate

Above query works fine but i need to convert this query so that i can compare Booking table BookingDate & Time with Events Table I am looking for performance based ms sql-query as data in these tables can grow with time.

Events

EventID EventStartDate  EventEndDate    EventDates  DT        EventStartTime    EventEndTime
17      2012-07-25      2012-07-26      2012-07-25  2012-07-25  10:00:00        12:00:00
17      2012-07-25      2012-07-26      2012-07-26  2012-07-26  10:00:00        12:00:00
21      2012-05-28      2012-05-28      2012-05-28  2012-05-28  18:00:00        19:00:00
26      2012-01-01      2015-01-01      2012-07-01  2012-07-01  08:00:00        18:00:00

Bookings

BookingID   TotalVisitors   BookingDate Time    BookingCancelled
1900221519  5               2012-07-10  10:30   0
5600515751  1               2012-07-20  10:30   0
8044913913  41              2012-07-05  10:30   0
1638934678  5               2012-07-20  10:30   1
5685687635  9               2012-07-25  10:30   0

In theabove case we assume user has change the date of first event to 2012-07-25 which now conflicts with the booking table as we have booking for this date. We need to find such rows so that users can take action accordingly such as intimate user about their booking cancellation & so on.

Scenario & Details

Let me give you a complete picture we allow tour of let us say private museum from Monday - Friday at two different times one at 10:30 am and second one at 04:30PM and for each tour we can take maximum of 50 Visitor (People). Suppose a group of 50 wants to visit museum on 2012-08-15 at 10:30am then we will block this date on front end. Frond end which is a simple web form with common fields like Name, Address, Company Name, No Of Visitors, Time (Which is a dropdown with two entries 10:30am,04:30pm) and Date of Visit (which is a calendar control that blocks the dates which has reached the full booking & will allow them to chose only those dates which are available.

On the other side Museum also organizes special event and they can block those dates on the booking form. Suppose an event which was scheduled for 2012-08-10 at 9:00am – 1:00pm & this date was blocked earlier by the admin. Now the date of event has changed to 2012-08-25. Since no event was scheduled for this date earlier so museum had some bookings for this date.

Since Museum can’t take any tours for people who have made booking for the same date 2012-08-25 needs to be informed. For this I need to compare the two table events table & booking table and find if we have any booking for 2012-08-25 at 10:30 if yes then we need to intimate the visitors by email about cancellation of their tours .

In my query I am also adding 59 minutes to time as we need to have a break of at least 1hour between EventStartTime or EventEndTime.

I hope this will give an idea of what kind of data I am looking for the report. Query which I have mentioned works fine but I need to pass value of date & time from booking table to events table for each row in booking table & show only those records from Booking table which clash with eventDate & time

Desired Output From the sample data I need a query which will show me following record only as if it conflicts with the booking after event date was changed

EventID BookingID   TotalVisitors   BookingDate Time    BookingCancelled
17      5685687635  9               2012-07-25  10:30   0

Answer 1

Assuming +1 hour actually means that a visit to museum lasts one hour, and this one hour must not be overlapped by some other museum activity, here is a query that finds such collisions. It is a simple join between two tables on date of booking/event, for booking that are not cancelled. Ovelapping interval is checked using expression explained here; essentially, two ranges overlap in start date of range A is before end date of range B AND end date of range A is after start date of range B. After and before might read after or equals or before or equals, depending how you define overlap.

select e.EventID,
       b.BookingID,
       b.TotalVisitors,
       b.BookingDate,
       b.Time,
       b.BookingCancelled
  from EventDatesReporting e
 inner join Bookings b
   -- On same date
    on e.EventDates = b.BookingDate
   -- Overlapping intervals
   and e.EventEndTime >= cast(b.Time as Time)
   and e.EventStartTime < dateadd (hour, 1, cast(b.Time as Time))
   -- Only if booking is not cancelled
   and b.BookingCancelled = 'false'

There is convenient SQL site called Sql Fiddle. I have put your sample and my answer there so you can check it online.