Enrollment Data Updates

[jbaldo]

Each year, both an enrollment per school site data set and a line item expenditure data set for measure G should be added to trackg.org. The enrollment data is used to calculate the $/student which is the only fair way to compare expenditures across school sites.

Data source for enrollment: https://www.cde.ca.gov/ds/sd/sd/filesenr.asp

Query for processing/unifying the data:

SELECT p.ncessch, latest.total_enrollment,  latest.year

FROM (SELECT n.cds_code, SUM(n.enr_total) as total_enrollment, LATESTYYYY as year

FROM ousd_YYYY_state_enrollment n

GROUP BY n.cds_code) latest, osud_ca_public_schools_directory p

WHERE latest.cds_code = p.cdscode

UNION

SELECT e.ncessch, e.total_enrollment, e.year FROM ousd_enrollment_LASTYYYY e

ousd_YYYY_state_enrollment = name of the new dataset
LATESTYYYY = year of the new dataset in YYYY format. For example, the 2016-17 school year would be 2016
ousd_enrollment_LASTYYYY = name for the new, unified table with enrollment data for all years