title: Compare data of one table with another based on date & Time
body: <p>I have to compare data between two table and check for any conflicts.</p>
  
  <p>Below is sample data for two table <code>Events &amp; Bookings</code> I need to compare <code>bookings</code> Table with <code>Events</code> Table based on <code>Date &amp; Time</code> &amp; show records which are in conflict.</p>
  
  <p>Actually user can change event date &amp; time once date is change user need to generate report that will show them if they already had any booking for that particular date &amp; time-slot.</p>
  
  <p>I am using following query to check if we have any booking for the updated events</p>
  
  <p><strong>Query</strong></p>
  
  <pre><code>Declare @bTime varchar(10) = '10:30';
  Declare @bDate date = '2012-08-15';
  SELECT * FROM  EventDatesReporting   WHERE 
  ((CONVERT(time(0), EventStartTime) &lt;= CONVERT(time(0),@bTime )  
      AND CONVERT(time(0), EventEndTime) &gt;= CONVERT(time(0),@bTime ))
  OR 
  (CONVERT(time(0), EventStartTime) &lt;= DATEADD(minute,59,CONVERT(time(0), @bTime))   
      AND CONVERT(time(0), EventEndTime) &gt;= DATEADD(minute,59,CONVERT(time(0), @bTime))))
  AND EventDates = @bDate
  </code></pre>
  
  <p>Above query works fine but i need to convert this query so that i can compare Booking table <code>BookingDate &amp; Time</code> with <code>Events Table</code> I am looking for performance based ms sql-query as data in these tables can grow with time.</p>
  
  <p><strong>Events</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>Bookings</strong></p>
  
  <pre><code>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
  </code></pre>
  
  <p>In theabove case we assume user has change the date of first event to  <code>2012-07-25</code> 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 &amp; so on.</p>
  
  <p><strong>Scenario &amp; Details</strong> </p>
  
  <p>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 &amp; will allow them to chose only those dates which are available. </p>
  
  <p>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 &amp; 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.</p>
  
  <p>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 &amp; 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 .</p>
  
  <p>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.  </p>
  
  <p>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 &amp; time from booking table to events table  for each row in booking table &amp; show only those records from Booking table which clash with eventDate &amp; time</p>
  
  <p><strong>Desired Output</strong>
  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</p>
  
  <pre><code>EventID BookingID   TotalVisitors   BookingDate Time    BookingCancelled
  17      5685687635  9               2012-07-25  10:30   0
  </code></pre>