Finding double bookings

I have two tables which when linked by a booking ID from table 1 and ID from table 2

Therefore

Table 1 contains BookingID, MemberID, BookedDate.
Table 2 contains ID, StartDateTime, EndDatetime

Im trying to flag double bookings where the same member id has booked twice within a time frame (StartDateTime and EndDateTime). Then remove the booking that has the latest BookedDate.

Can someone help me with a sql script for this?

Hope that makes sense.

Thanks in advance

Ian

Answer

If I understand correctly, you need the member id from the first table and then you are just looking for overlaps:

with t as (
      select t2.*, t1.memberid
      from table2 t2 join
           table1 t1
           on t2.id = t1.bookid
     )
select t.*
from t
where exists (select 1
              from t t2
              where t2.memberid = t.memberid and
                    t2.id <> t.id and
                    t2.startdate < t.enddate and
                    t2.enddate > t.startdate
             );