Category: Infinite Campus

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: , snap.snapDate.

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!