There is a table like this (‘work_planned’). Columns with dates, type DATETIME.
| id | id_cat | wrk1_start | wrk1_end | wrk2_start | work2_end |
|---|---|---|---|---|---|
| 54 | 5 | 2024-06-18 | 2024-06-20 | 2024-06-21 | 2024-06-24 |
| 58 | 6 | 2024-04-25 | 2024-09-28 | NULL | NULL |
| 64 | 5 | 2024-07-10 | 2024-07-18 | 2024-07-19 | 2024-07-26 |
| 67 | 6 | 2024-06-17 | 2024-08-30 | NULL | NULL |
| 77 | 5 | 2024-07-16 | 2024-07-18 | 2024-07-19 | 2024-07-24 |
| 80 | 5 | 2024-07-20 | 2024-07-23 | 2024-07-24 | 2024-07-27 |
| 82 | 5 | 2024-07-22 | 2024-07-28 | 2024-07-29 | 2024-08-06 |
I make a query, the meaning of which is approximately as follows:
SELECT * FROM `work_planned`
WHERE `wrk1_start` >= '2024-07-01' AND `wrk1_start` <= '2024-07-31'
OR `wrk1_end` >= '2024-07-01' AND `wrk1_end` <= '2024-07-31';
Get the result:
| id | id_cat | wrk1_start | wrk1_end | wrk2_start | work2_end |
|---|---|---|---|---|---|
| 64 | 5 | 2024-07-10 | 2024-07-18 | 2024-07-19 | 2024-07-26 |
| 77 | 5 | 2024-07-16 | 2024-07-18 | 2024-07-19 | 2024-07-24 |
| 80 | 5 | 2024-07-20 | 2024-07-23 | 2024-07-24 | 2024-07-27 |
| 82 | 5 | 2024-07-22 | 2024-07-28 | 2024-07-29 | 2024-08-06 |
The thing is that records with id 58, 67 contain works in July (month 07), but they lack key points
(‘wrk1_start’, ‘wrk1_end’) for the month of the query.
Is there a way for these records to be included in the result?
| id | id_cat | wrk1_start | wrk1_end | wrk2_start | work2_end |
|---|---|---|---|---|---|
| 64 | 5 | 2024-07-10 | 2024-07-18 | 2024-07-19 | 2024-07-26 |
| 77 | 5 | 2024-07-16 | 2024-07-18 | 2024-07-19 | 2024-07-24 |
| 80 | 5 | 2024-07-20 | 2024-07-23 | 2024-07-24 | 2024-07-27 |
| 82 | 5 | 2024-07-22 | 2024-07-28 | 2024-07-29 | 2024-08-06 |
| 58 | 6 | 2024-04-25 | 2024-09-28 | NULL | NULL |
| 67 | 6 | 2024-06-17 | 2024-08-30 | NULL | NULL |
Let me further explain the essence of the question.
Record id=67, has a range of 2024-06-17 – 2024-08-30
The search is performed between the dates 2024-07-01 – 2024-07-31, i.e. everything that is in July.
Question – is it possible to get this record in this search by any method?
Thanks in advance.
6
Intersection for ranges a(start,end) and b(start,end) can be tested as
a.start <= b.end AND a.end >= b.start
See example
create table work_planned( id int, id_cat int, wrk1_start date,wrk1_end date
,wrk2_start date, work2_end date);
insert into work_planned values
(64,5,'2024-07-10','2024-07-18','2024-07-19','2024-07-26') -- inner
,(77,5,'2024-07-16','2024-07-18','2024-07-19','2024-07-24') -- inner
,(80,5,'2024-07-20','2024-07-23','2024-07-24','2024-07-27') -- inner
,(82,5,'2024-07-22','2024-07-28','2024-07-29','2024-08-06') -- inner
,(58,6,'2024-04-25','2024-09-28', NULL, NULL) -- overlap
,(67,6,'2024-06-17','2024-08-30', NULL, NULL) -- overlap
,(01,6,'2024-04-25','2024-07-01', NULL, NULL) -- intersect on left edge
,(02,6,'2024-07-31','2024-08-01', NULL, NULL) -- intersect on right edge
,(03,6,'2024-08-17','2024-08-30', NULL, NULL) -- after range
,(04,6,'2024-04-17','2024-06-30', NULL, NULL) -- before range
,(05,6,'2024-06-17','2024-07-05', NULL, NULL) -- intersect left side
,(06,6,'2024-07-25','2024-08-31', NULL, NULL) -- intersect right side
;
SELECT * FROM `work_planned`
WHERE `wrk1_start` <= '2024-07-31' AND `wrk1_end` >= '2024-07-01'
;
| id | id_cat | wrk1_start | wrk1_end | wrk2_start | work2_end |
|---|---|---|---|---|---|
| 64 | 5 | 2024-07-10 | 2024-07-18 | 2024-07-19 | 2024-07-26 |
| 77 | 5 | 2024-07-16 | 2024-07-18 | 2024-07-19 | 2024-07-24 |
| 80 | 5 | 2024-07-20 | 2024-07-23 | 2024-07-24 | 2024-07-27 |
| 82 | 5 | 2024-07-22 | 2024-07-28 | 2024-07-29 | 2024-08-06 |
| 58 | 6 | 2024-04-25 | 2024-09-28 | null | null |
| 67 | 6 | 2024-06-17 | 2024-08-30 | null | null |
| 1 | 6 | 2024-04-25 | 2024-07-01 | null | null |
| 2 | 6 | 2024-07-31 | 2024-08-01 | null | null |
| 5 | 6 | 2024-06-17 | 2024-07-05 | null | null |
| 6 | 6 | 2024-07-25 | 2024-08-31 | null | null |
fiddle
1