MySQL - Selecting "Everyone who booked last January, but hasn't booked this January".

Question

I am having a few issues with what is probably a reasonably simple SQL statement - unfortunately, my skills don't go as far as this and I can't get my head round it!

I have, for example, the following tables:

• booking_record
• client

Booking record may have many bookings for one client, they are linked via 'labelno'.

I would like to select the clients who have booked last but not booked this . I.e. This client has booked this month last year, but hasnt booked this year.

I only got as far as selecting either situation, for example:

Select client_.title, 
       client_.initial, 
       client_.surname, 
       client_.labelno,
       client_.email, 
       booking_record_.bookingdate
From booking_record booking_record_ 
Inner Join client client_ On booking_record_.labelno = client_.labelno
Where client_.email Not Like '' 
  And Date_Format(booking_record_.bookingdate, '%m') = ?Param1 
  And Date_Format(booking_record_.bookingdate, '%Y') = Year(AddDate(Now(), Interval -1 Year))
Group By client_.labelno

I had a quick look at views, but this is being used within a report designer which doesn't appear to support views (or at least, not at design time). Is it possible to get what I need without the use of views?

Thanks, Daniel.

Answer 1

    Select c.title, 
           c.initial, 
           c.surname, 
           c.labelno,
           c.email
    from
        client c
    where exists (select * from booking_record where labelno=c.labelno
        and bookingdate between @start1 and @end1)
    and not exists (select * from booking_record where labelno=c.labelno
        and bookingdate between @start2 and @end2)

This takes start1 and end1 as the dates for last year's booking window, and start2 and end2 for this year's, and should return a list of all the clients in the first list but not the second. Sorry if the variable format code is wrong for you, it's a while since I've done MySQL but the rest should be fine - you want a double EXISTS check in your where clause.

Answer 2

Since I don't work much with mySQL and don't know all of the date functions I've just used descriptions for the dates below. Try to write the query that way instead of using functions on the table columns since that can preclude the use of indexes.

SELECT
     C.title,
     C.initial,
     C.surname,
     C.labelno,
     C.email,
     BR.bookingdate
FROM
     Booking_Record BR1
INNER JOIN Client C ON
     C.labelno = BR.labelno AND
     C.email NOT LIKE ''
WHERE
     BR1.bookingdate >= <jan. 1st last year> AND
     BR1.bookingdate < <feb. 1st last year> AND
     NOT EXISTS
     (
          SELECT
               *
          FROM
               Booking_Record BR2
          WHERE
               BR2.labelno = BR1.labelno AND
               BR2.bookingdate >= <jan. 1st this year> AND
               BR2.bookingdate < <feb. 1st this year>

Answer 3

Use:

SELECT c.title,
       c.initial,
       c.surname,
       c.labelno,
       c.email,
       br.booking date
  FROM CLIENT c
  JOIN BOOKING_RECORD br ON br.labelno = c.labelno
                        AND br.bookingdate BETWEEN STR_TO_DATE(?Param1, '%Y-%m-%d')
                                               AND STR_TO_DATE(?Param2, '%Y-%m-%d')
 WHERE c.label_no NOT IN (SELECT t.label_no
                            FROM BOOKING_RECORD t
                           WHERE br.bookingdate BETWEEN STR_TO_DATE(?Param3, '%Y-%m-%d')
                                                    AND STR_TO_DATE(?Param4, '%Y-%m-%d')

• Param1 & 2 represent the dates in the past you want to check for the existence of IE Jan of last year.
• Param3 & 4 represent the dates you want to check for that the client does not have.
• Using functions on columns for evaluation means that, assuming an index exists for the column, the index can not be used.

Caveat:

---

In MySQL, NOT IN and LEFT JOIN/IS NULL are faster than NOT EXISTS. For more information, see this link for details.

Reference:

---

• STR_TO_DATE
• DATE_FORMAT: For the list of supported date masks to use in STR_TO_DATE