I was tasked with getting a count of special education students historically. As always, I wanted a solution that was elegant, future-proof, and easily manipulated. The completed code is at the bottom of the post with an explanation between!
Let’s break it down
We begin by gathering the foundational data of the query: the school year, the school name, and the enrollments which we will use to count or aggregate later.
SELECT DISTINCT cal.endYear , sch.name FROM Calendar cal INNER JOIN School sch ON cal.schoolID = sch.schoolID INNER JOIN Enrollment e ON cal.calendarID = e.calendarID WHERE e.specialEdStatus = 'Y' ORDER BY 1,2;
We are selecting with the DISTINCT clause and we don’t have any enrollment data in the SELECT list which means that we will see the result we will end up aggregating to. Just know that the enrollment data exists behind the scenes, we just aren’t selecting it.
Now, we want to make it so each row in our existing result set, has a record for each snapshot date. We also want to do this so we don’t have to put string literal dates for every snapshot. My solution is a CROSS JOIN to a query that builds a list of every calendar year’s set of snapshot dates. We also include the calendar year for each record to match on later. The query can be ran standalone to see the result set.
SELECT CONCAT((cal.endYear-1),'-10-15') AS snapDate, cal.endYear FROM Calendar cal UNION SELECT CONCAT(cal.endYear,'-03-15') AS snapDate, cal.endYear FROM Calendar cal UNION SELECT CONCAT(cal.endYear,'-05-15') AS snapDate, cal.endYear FROM Calendar cal;
Now that we have our snapshot dates, we need to CROSS JOIN it with our previous result set.
CROSS JOIN ( SELECT CONCAT((cal.endYear-1),'-10-15') AS snapDate, cal.endYear FROM Calendar cal UNION SELECT CONCAT(cal.endYear,'-03-15') AS snapDate, cal.endYear FROM Calendar cal UNION SELECT CONCAT(cal.endYear,'-05-15') AS snapDate, cal.endYear FROM Calendar cal ) snap
Since this will generate every possible combination (Cartesian Product or Table A x Table B) of the result of the CROSS JOIN, we need to filter out the non-matching records. So we add to our WHERE clause
WHERE cal.endYear = snap.endYear. We also add our new snapshot date to our select list:
We add a line to check that the student’s enrollment record is active on the snapshot date.
AND snap.snapDate BETWEEN e.startDate AND ISNULL(e.endDate, CONCAT(cal.endYear,'-05-15'))
Finally, we finish it off by aggregating the count of students that are marked as Special Education for each record by adding the aggregate function to our select list:
, COUNT(DISTINCT e.personID) AS snapCount
and a GROUP BY clause to designate how we want to aggregate (in our case, by the year, school, and date):
GROUP BY cal.endYear, sch.name, snap.snapDate
The final product
You can find the completed query below. Thanks for reading and I hope this helps!