Mysql Filter two dates between two columns

Question

I'm trying to get result which will be filter two dates between two dates. To give more background - I have a table with employee holidays and I want to get all dates when employees are on holiday in selected date range.

Example:

Employee 1 have holidays in date range from 2022-07-20 to 2022-07-30

Employee 2 have holidays in date range from 2022-07-22 to 2022-07-25

Table looks like:

ID    employee_id  StartDate      EndDate
 1      1          2022-07-20    2022-07-30
 2      2          2022-07-22    2022-07-25

And what I want to do is filter all employees who have holidays in date range 2022-07-21 - 2022-07-28

When I'm trying something like this:

SELECT * FROM emp_holidays WHERE StartDate >= '2022-07-21' and EndDate <= '2022-07-28'

Then I'm getting only second record from database, because only one employee is matching this filter, but I know on this date rande first employee is also on holidays.

What will be the best result?

eployee_id date
1          2022-07-21
1          2022-07-22
1          2022-07-23
1          2022-07-24
1          2022-07-25
1          2022-07-26
1          2022-07-27
1          2022-07-28
2          2022-07-22
2          2022-07-23
2          2022-07-24
2          2022-07-25

Another amazing result will be

eployee_id date_from  date_to
1          2022-07-21 2022-07-25
2          2022-07-22 2022-07-25

Any help will be amazing :)

I'm thinking about new helper table with dates for next ~50 years, and then getting datas from helper tab, and then checking if StartDate is in select from helper table result, but I'm not sure if this is the best solution.

I was trying a lot of queries, we can use everything what we want. OR, AND, Helper Table, Procedures etc. But I don't know how it should look. I can get date from date range, but when I need to get all dates when employees are on holiday it is really hard for me. — Jakub Jóźwiak
– Jakub Jóźwiak, Commented Jul 27, 2022 at 0:35
@BagusTesa I've update already my question with last paragraph. Please let me know it is a good solution. — Jakub Jóźwiak
– Jakub Jóźwiak, Commented Jul 27, 2022 at 0:40
The ranges overlaps when each range start is less than opposite range end. So WHERE StartDate <= '2022-07-28' and EndDate >= '2022-07-21'. The overlapping range is from the greatest of starts till the least of ends. — Akina
– Akina, Commented Jul 27, 2022 at 4:46

Bagus Tesa · Accepted Answer · 2022-07-27 13:52:16Z

Akina is right, I didn't think that way earlier (silly me). Expanding upon their comment, we can use CASE function to pick the matching dates for StartDate and EndDate. Given the filter is for getting employee's holiday between 21-07-2022 and 28-07-2022, we can make query like this:

select
  id, employee_id,
  case when StartDate >='2022-07-21'
       then StartDate
       else '2022-07-21' end
       as StartDate,
  case when EndDate <= '2022-07-28'
       then EndDate
       else '2022-07-28' end
       as EndDate
from holidays
where StartDate <= '2022-07-28' and EndDate >= '2022-07-21'

It will outputs the following:

id	employee_id	StartDate	EndDate
1	1	2022-07-21	2022-07-28
2	2	2022-07-22	2022-07-25
3	3	2022-07-21	2022-07-23
4	4	2022-07-22	2022-07-28

See more on Fiddle: https://www.db-fiddle.com/f/oDs1SkhdeRP2jznyrzVArk/0

Though, I'm not quite sure why on your second example (with the constrained StartDate and EndDate) it shows 25 instead of 28 for the one with EndDate at 30.

Collectives™ on Stack Overflow

Mysql Filter two dates between two columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related