My table presences holds time ranges, where an employee is present. The range has a start date and end date.
I know how to select a date being in a date range, by simply using BETWEEN but, what I want to do, is to provide a given date range, e.g. 2022-04-04 to 2022-04-06 and I want to get all rows where an employee as available at at least one day of the given range.
When the presences table has a row with start 2022-01-01 and end 2022-12-31, what means, the employee is present every day, then the row must be returned, because the provided range is in the presence range.
Another example, an employee is present on 2022-04-05 only, then this row must be returned as well, as the employee is present at least once in the provide range.
Here is a fiddle from what I got so far: http://sqlfiddle.com/#!9/2b3506/4
You can compare the date value of start to the end date and the date value of end to the start date of the queried date range:
SELECT *
FROM presences
WHERE DATE(start) <= '2022-04-06' AND DATE(end) >= '2022-04-04';
See the demo.