CODEWITHSUNDEEP
sql
Izzy
Izzy

Reputation: 192

SQL - searching for booking schedules where all bookings for date range have been cancelled

I'm querying a booking system, and I need to identify all of the booking schedules (which represent recurring bookings), where all booking for a specified date range have been cancelled. Why is the following query giving me back booking schedules that contain bookings during the specified date range that have been cancelled?

SELECT DISTINCT b.BookingScheduleId FROM Booking b
 WHERE b.CancellationStatus = 1
   AND b.[Date] BETWEEN '01-Nov-14' AND '31-Dec-14'
   AND NOT EXISTS (
       SELECT * FROM Booking ref
        WHERE ref.BookingScheduleId = b.BookingScheduleId
          AND ref.[Date] BETWEEN '01-Nov-14' AND '31-Dec-14'
          AND b.CancellationStatus = 0
       )
   AND b.BookingScheduleId IS NOT NULL

Upvotes: 0

Views: 51

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I would start by writing the query using ANSI standard dates:

SELECT DISTINCT b.BookingScheduleId
FROM Booking b
WHERE b.CancellationStatus = 1 AND
      b.[Date] BETWEEN '2014-01-14' AND '2014-12-31' AND
      NOT EXISTS (SELECT 1
                  FROM Booking ref
                  WHERE ref.BookingScheduleId = b.BookingScheduleId AND
                        ref.[Date] BETWEEN '2014-01-14' AND '2014-12-31' AND
                        b.CancellationStatus = 0
 -----------------------^
                 ) AND
      b.BookingScheduleId IS NOT NULL;

Then I would note the underlined part and rephrase a query a bit:

SELECT s.BookingScheduleId
FROM (SELECT DISTINCT BookingScheduleId
      FROM Booking b
      WHERE b.CancellationStatus = 1 AND
            b.[Date] BETWEEN '2014-01-14' AND '2014-12-31' AND
            b.BookingScheduleId IS NOT NULL
     ) s
WHERE  NOT EXISTS (SELECT 1
                   FROM Booking ref
                   WHERE ref.BookingScheduleId = s.BookingScheduleId AND
                         ref.[Date] BETWEEN '2014-01-14' AND '2014-12-31' AND
                         ref.CancellationStatus = 0
                  );

I prefer to the do the filtering and distinct as a subquery. It is probably faster (although I do not know without looking at the execution plan or measuring the performance), but it is clearer to me, because the subquery gets the candidate schedules and the outer one does the filtering using the not exists. By the way, you could also phrase this query as an aggregation query, but I left it in this format because that seems to be your intention.

Upvotes: 1

Related Questions