Question 21862 in Mysql Question posted in
The official MySQL documentation can be found here.

How to find overlapping dates – Mysql

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
Tags:

2

Answers


  1. 0

    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

    See the dbfiddle

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search