I am making a student web app. Amongst other tables, I have a table in which students enroll and enrollments are between two dates.
This app uses MySQL 5.6 and PHP 7.2
It has the following fields:
- IDStudent
- StartDate
- EndDate
- IDCourse
Each course has a maximum capacity in which it cannot be surpassed.
I want to know, given a start date, end date and IDCourse, how many concurrent students are in a course. I get an approxiumate value just counting rows between two dates
SELECT COUNT(*) FROM enrollments
WHERE IDCourse = ?
AND (
(StartDate BETWEEN "<start date>" AND "<end date>")
OR
(EndDate BETWEEN "<start date>" AND "<end date>")
OR
(StartDate <= "<start date>" AND EndDate>= "<end date>")
)
But that doesn’t take account non overlapping ranges. It counts every enrollment.
For example, I have this very simple case:
Want to find how many students are enrolled between 01/01/2021 and 05/01/2021 at a specified course
And I have those 3 enrollments on that course:
- 01/01/2021 – 02/01/2021
- 03/01/2021 – 04/01/2021
- 20/12/2020 – 01/02/2021
I should get 2 count and not 3, because 1 and 2 don’t overlap while 3 overlaps both.
I tried to search online but I didn’t found something similar, maybe I am not using the correct keywords!
Many thanks for your help
Regards