How to return order_no concurrent bookings

Question

0.00/5 (No votes)

See more:

I have a table on booking orders 
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 (customer made a new booking before they completed or cancelled the previous booking).

Explanation
Customer A booked #000 at 1:11, and completed it at 1:25. 
Customer A booked #001 at 1:18, and completed it at 1:30. 
Customer A booked #002 at 5:30, and completed it at 6:10. 
Customer B booked #020 at 1:50, and completed it at 2:00. 
Customer B booked #021 at 5:35, and completed it at 6:05.
 
Only Customer A had a concurrent booking. The correct query would return order_no #000 and #001.


I need help with this, Please someone help me

What I have tried:

<pre>
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

Posted 4-Dec-20 21:25pm

098828

Updated 7-Dec-20 2:19am

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-12-04T21:34:00

We are more than willing to help those that are stuck: but that doesn't mean that we are here to do it all for you! We can't do all the work, you are either getting paid for this, or it's part of your grades and it wouldn't be at all fair for us to do it all for you.

So we need you to do the work, and we will help you when you get stuck. That doesn't mean we will give you a step by step solution you can hand in!
Start by explaining where you are at the moment, and what the next step in the process is. Then tell us what you have tried to get that next step working, and what happened when you did.

If you are having problems getting started at all, then this may help: How to Write Code to Solve a Problem, A Beginner's Guide[^]

Richard Deeming · Answer 2 · 2020-12-07T02:19:00

Try:

SQL

SELECT
    *
FROM
    Bookings As a
WHERE
    Exists
    (
        SELECT 1
        FROM Bookings As b
        WHERE b.user_id = a.user_id
        And b.order_no != a.order_no
        And b.booking_time < IsNull(a.complete_time, a.cancel_time)
        And a.booking_time < IsNull(b.complete_time, b.cancel_time)
    )
;

Demo[^]

How to return order_no concurrent bookings

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0