How to find overlapping dates-CodePudding

What I need is to return all records that may overlap each other.

-- Create Temp Table

CREATE TABLE `abc` (
`id` int(11) NOT NULL,
`propertie_id` int(11) NOT NULL,
`rooms_id` int(11) NOT NULL,
`block_name` varchar(256) NOT NULL,
`check_in` date NOT NULL,
`check_out` date NOT NULL,
`status` tinyint(2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert records

INSERT INTO `abc` (`id`, `propertie_id`, `rooms_id`, `block_name`, `check_in`, `check_out`, `status`) VALUES
(1, 33, 5, 'BeachHouse', '2022-09-01', '2022-09-04', 3),
(2, 33, 5, 'BeachHouse', '2022-09-04', '2022-09-06', 2),
(3, 33, 5, 'BeachHouse', '2022-11-28', '2022-12-04', 1),
(4, 33, 5, 'BeachHouse', '2022-12-04', '2022-12-14', 13),
(5, 44, 6, 'C3', '2022-09-24', '2022-09-26', 2),
(6, 44, 16, 'C3', '2022-09-26', '2022-09-29', 13),
(7, 46, 12, '8BR', '2022-12-26', '2023-01-11', 2),
(8, 46, 12, '8BR', '2023-01-01', '2023-01-11', 1),
(9, 47, 4, 'Skye', '2022-12-21', '2023-01-01', 3),
(10, 47, 4, 'Skye', '2023-01-01', '2023-01-03', 1);

My result would look like this: See the screenshot

| pro_id | rooms_id | block_name | check_in   | check_out  | status |
| -----  | -------- | ---------- | ---------- | ---------- | ------ |
| 43     | 4        | Oria       | 2023-01-01 | 2023-01-11 | 2      |
| 43     | 4        | Oria       | 2023-01-06 | 2023-01-11 | 1      |

**I have added two more new duplicate/overlapping entries to get the exact value and better understand

But I want to only red marking rows(in the screenshot) and ignore other rows when checkout is 2022-09-04 overlapping with check-in 2022-09-04

I have tried with this

SELECT o.propertie_id,o.rooms_id,o.block_name,o.check_in,o.check_out,o.status 
FROM vi_bookings o
WHERE EXISTS
(
 SELECT 1 FROM vi_bookings o2
 WHERE o2.`propertie_id` = o.`propertie_id`
 AND o2.`rooms_id` = o.`rooms_id`
 AND o2.`block_name` = o.`block_name`
 AND o2.check_in <= o.check_out 
 AND o.check_in <= o2.check_out 
 AND o2.id != o.id
)
AND o.status NOT IN (4,10)
ORDER BY o.`propertie_id`, o.`block_name`, o.check_in

CodePudding user response：

The ranges overlaps if each range start is less than opposite range end. So

SELECT t1.*, t2.*
FROM abc t1
JOIN abc t2 USING (propertie_id, rooms_id, block_name)
WHERE t1.check_in < t2.check_out   -- check
  AND t2.check_in < t1.check_out   -- for overlapping
  AND t1.id < t2.id;      -- and avoid duplicates (a-b and b-a)

This query returns overlapped rows pairs.

If you need a data for one table copy only then remove AND t1.id < t2.id and use SELECT DISTINCT t1.* ...

If rows with some status values must be ignored then add according conditions for both t1 and t2 table copies.

CodePudding user response：

You are close. With a minor change you should see the result you wanted:

SELECT o.propertie_id,o.rooms_id,o.block_name,o.check_in,o.check_out,o.status 
FROM vi_bookings o
WHERE EXISTS
(
 SELECT 1 FROM vi_bookings o2
 WHERE o2.`propertie_id` = o.`propertie_id`
 AND o2.`rooms_id` = o.`rooms_id`
 AND o2.`block_name` = o.`block_name`
 AND o2.check_in < o.check_out AND o2.check_out > o.check_in
 AND o2.id != o.id
)
AND o.status NOT IN (4,10)
ORDER BY o.`propertie_id`, o.`block_name`, o.check_in