eal-students-with-levels.sql

/*
    This SQL query exports all EAL students with their levels, from all schools.
*/

SELECT
	BASE_SKL.SKL_SCHOOL_NAME AS 'Student Base School',
	STD.STD_NAME_VIEW AS 'Student name',
	STD.STD_ENROLLMENT_STATUS as 'Student Status',
	STD.STD_GRADE_LEVEL AS 'Grade',
	PGM.PGM_PROGRAM_CODE AS 'Program',
	PGM.PGM_FIELDA_001 AS 'Level',
	PGM.PGM_FIELDA_003 AS 'Program School DAN',
	COALESCE ((SELECT SKL_SCHOOL_NAME FROM MSS_SCHOOL WHERE SKL_SCHOOL_ID=PGM.PGM_FIELDA_003), 'UNKNOWN OUT-OF-DIVISION SCHOOL') as 'Program School',
	FORMAT(PGM.PGM_START_DATE, 'MM-dd-yyyy') AS 'Start Date',
	FORMAT(PGM.PGM_END_DATE, 'MM-dd-yyyy') AS 'End Date'
FROM
	MSS_STUDENT STD
	INNER JOIN MSS_STUDENT_PROGRAM_PARTICIPATION PGM ON STD.STD_OID = PGM.PGM_STD_OID
	INNER JOIN MSS_SCHOOL BASE_SKL ON STD.STD_SKL_OID = BASE_SKL.SKL_OID
WHERE
	PGM.PGM_PROGRAM_CODE = 'EAL' AND PGM.PGM_END_DATE > GETDATE()
-- Uncomment the line below to filter out specific statuses
--	AND STD.STD_ENROLLMENT_STATUS NOT IN ('Withdrawn', 'Comp Sch')
ORDER BY
	BASE_SKL.SKL_SCHOOL_NAME,
	STD.STD_GRADE_LEVEL,
	STD.STD_NAME_VIEW