how to find values between two columns and compare them to others?-CodePudding

Good day,

I have the following table:

I am trying to figure how to calculate the overlapping reservations for each reservation ID. The result should look like this:

e.g. Reservation ID 1 goes from Day 2 to Day 5, thus is overlaps with Reservation ID 2 and Reservation ID 5.

I would like to include something I have tried but they are so far off and not even close to being correct.

CodePudding user response：

You can use a self LEFT join and aggregation:

SELECT r1.id, COUNT(r2.id) count
FROM reservations r1 LEFT JOIN reservations r2
ON r2.id <> r1.id AND r2.beginning_day <= r1.ending_day AND r2.ending_day >= r1.beginning_day
GROUP BY r1.id;

Or, simpler with a correlated subquery:

SELECT r1.id,
       (
         SELECT COUNT(*) 
         FROM reservations r2 
         WHERE r2.id <> r1.id
           AND r2.beginning_day <= r1.ending_day AND r2.ending_day >= r1.beginning_day
       ) count
FROM reservations r1;

See the demo.