I have a table on booking orders for trips.
Bookings (order_no, user_id, booking_time,cancel_time,complete_time)
I try to write a query to return the order_no from all rows where customers made concurrent bookings (i.e. where a customer made a new booking before they completed or cancelled the previous booking).
Customer A booked an order #000 at 1:00, and completed it at 1:25.
Customer A booked an order #001 at 1:19, and completed it at 1:30.
Customer A booked an order #002 at 3:30, and completed it at 4:00.
Customer B booked an order #020 at 1:10, and completed it at 2:00.
Customer B booked an order #021 at 4:00, and completed it at 4:05.
Only Customer A had a concurrent booking. The query would return order_no #000 and #001.
FROM Bookings a JOIN Bookings b ON a.user_id = b.user_id AND a.order_no <> b.order_no
(b.booking_time < a.complete_time OR b.booking_time < a.cancel_time) AND
(b.complete_time > a.booking_time OR b.cancel_time> a.booking_time
the issue is some record have complete_time but cancel_time is null, while the others have cancel_time but complete_time is null
I need help with this, Please someone help me