Finding ocuppations via SQL and/or PHP

Question

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! I found Determine max number of overlapping DATETIME ranges but that is for MySQL 8

Many thanks for your help Regards

You do get 2 as far as I can tell: http://sqlfiddle.com/#!9/cf3e263/1 ... perhaps double-check your data/code to ensure there are no errors not evident from the above. — Markus AO, Dec 21 '21 at 11:31
@MarkusAO I think that in the sqlfiddle one of the dates has month and day transposed — Jayvee, Dec 21 '21 at 11:39
Right, I see the dates are all over the place. Result of turning timestamps around huh. @Christian could you please provide us with a schema/fiddle to spare us from recreating this manually, and provide any dates in the format they apppear in your SQL data. Here's a redo of the fiddle: http://sqlfiddle.com/#!9/2e927a/1 — Markus AO, Dec 21 '21 at 11:43
@Christian, for one, you need to `AND` instead of `OR` on the `BETWEEN` conditions, in parentheses for a single criteria. Then, the third condition's `<= and `>=` seem to be backwards. How's this? http://sqlfiddle.com/#!9/2e927a/2 ... at least it gives the right count here. ^_^ — Markus AO, Dec 21 '21 at 11:52
@MarkusAO that is showing the 2 that don't overlap, I think the OP is looking for the ones that do overlap. Suppose there is another student enrolled between 20/12/2020 and 01/02/2021, then the count should be 3. That's what I think the only way is by creating a calendar for the period to be analysed. But the OP has gone mute :) — Jayvee, Dec 21 '21 at 12:16
Right, foggy head here. Still re-reading the OP, "_enrolled between 01/01/2021 and 05/01/2021_". Really, there are three students that have been enrolled at some point during those dates. Then the question seems to be, "What is the maximum amount of students enrolled on any given day between two given dates?". I'm beginning to wonder if an altogether different approach to course capacity tracking would be in order. — Markus AO, Dec 21 '21 at 13:09

score 0 · Answer 1 · answered Dec 21 '21 at 11:23

I think you may need to create a calendar table between the first start date and the last end date, count by date and then select the max between the period you are interested:

select max(stcount)
from
(
select c.dt, count(*) stcount from calendar_table c
join enrollments e on c.dt between e.StartDate and e.EndDate
group by c.dt
 ) countbydate
 where dt between '2021-01-01' and '2021-01-05'

db-fiddle:

https://www.db-fiddle.com/f/dXuKMoRQ2ivLt5qi5AVFcG/0

Finding ocuppations via SQL and/or PHP

1 Answers1