Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to return order_no where customers made concurrent bookings?

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 273
    Answer it

    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.

    Sample Output

    #000

    #001

     

    SELECT a.user_id

    FROM Bookings a JOIN Bookings b ON a.user_id = b.user_id AND a.order_no <> b.order_no

    WHERE

        (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

 0 Answer(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: