presences holds time ranges, where an employee is present. The range has a
start date and
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.
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:
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.
Answered By – forpas
Answer Checked By – Katrina (BugsFixing Volunteer)