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.

CodePudding user response：

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:

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.