jordans_principle_attendance_report.sql

DECLARE 
	@STARTDATE AS DATETIME, 
	@ENDDATE AS DATETIME

SET 
	@STARTDATE = '2023-06-13'
SET
	@ENDDATE = '2023-06-28'

SELECT 
	T1.Student,
	T1.Grade,
	T1.School,
	CASE WHEN T3.TotalAbsences is NULL THEN 0 ELSE T3.TotalAbsences END AS "Total Absences",
	T2.Expected,
	FORMAT(CASE WHEN T2.Since < @STARTDATE THEN @STARTDATE ELSE T2.Since END, 'MMM/dd/yyyy') AS "Start Date",
	FORMAT(CASE WHEN T2.Since < @ENDDATE THEN @ENDDATE ELSE T2.Since END, 'MMM/dd/yyyy') AS "End Date",
	CASE WHEN T3.TotalAbsences > 0 THEN FORMAT((T3.TotalAbsences * 100.00 / T2.Expected), 'N', 'en-us') ELSE '0' END AS "Absent Percentage"
FROM
	(SELECT 
		ST.STD_NAME_VIEW AS Student,
		SC.SKL_SCHOOL_NAME AS School,
		ST.STD_GRADE_LEVEL AS Grade
	FROM
		MSS_GROUP_MEMBER GRMB
	INNER JOIN	
		MSS_STUDENT ST ON GRMB.GMB_OBJ_OID = ST.STD_PSN_OID
	INNER JOIN
		MSS_SCHOOL SC ON ST.STD_SKL_OID = SC.SKL_OID
	WHERE
		GMB_GRP_OID = 'GRP000000cx7dd' AND ST.STD_ENROLLMENT_STATUS IN ('Active', 'Active No Primary') ) T1  
LEFT JOIN
	(SELECT 
		COUNT(ST.STD_NAME_VIEW) AS Expected,
		ST.STD_NAME_VIEW AS Student,
		STEN.ENR_ENROLLMENT_DATE AS Since
	FROM
		MSS_STUDENT ST
	INNER JOIN 
		MSS_STUDENT_SCHEDULE STSC ON ST.STD_OID = STSC.SSC_STD_OID
	INNER JOIN
		MSS_STUDENT_ENROLLMENT STEN ON ST.STD_OID = STEN.ENR_STD_OID AND ST.STD_SKL_OID = STEN.ENR_SKL_OID AND STEN.ENR_OID IN (SELECT TOP 1 ENR_OID FROM MSS_STUDENT_ENROLLMENT WHERE ST.STD_OID = ENR_STD_OID AND STD_ENROLLMENT_STATUS = 'Active' AND ST.STD_SKL_OID = ENR_SKL_OID)  
	INNER JOIN
		MSS_SCHEDULE_MASTER SCM ON STSC.SSC_MST_OID = SCM.MST_OID
	INNER JOIN
		MSS_COURSE_SCHOOL MCS ON SCM.MST_CSK_OID = MCS.CSK_OID
	INNER JOIN
		DBO.MSS_CALENDAR_SCHOOL CAL ON ST.STD_SKL_OID = CAL.CAS_SKL_OID AND ST.STD_CALENDAR_CODE = CAL.CAS_CALENDAR_ID
	INNER JOIN
		DBO.MSS_CALENDAR_SCHOOL_DATE CALD ON CAL.CAS_OID = CALD.CSD_CAS_OID
	INNER JOIN
		MSS_SCHEDULE SCH ON SCM.MST_SCH_OID = SCH.SCH_OID
	WHERE
		CALD.CSD_DATE BETWEEN CASE WHEN STEN.ENR_ENROLLMENT_DATE > @STARTDATE THEN STEN.ENR_ENROLLMENT_DATE ELSE @STARTDATE END AND @ENDDATE
		AND MCS.CSK_ATTENDANCE_HIDE_IND = 0 AND CALD.CSD_IN_SESSION_IND = 1
		AND SCH.SCH_START_DATE >= '2022-08-01' AND SCH.SCH_END_DATE < '2023-08-01'
	GROUP BY
		ST.STD_NAME_VIEW,
		STEN.ENR_ENROLLMENT_DATE) T2 
ON
	T1.Student = T2.Student
LEFT JOIN
	 (SELECT 
		ST.STD_NAME_VIEW AS Student,
		COUNT(ATT.PAT_STD_OID) AS TotalAbsences
	FROM
		DBO.MSS_STUDENT ST 
	LEFT JOIN
		DBO.MSS_STUDENT_PERIOD_ATTENDANCE ATT ON ST.STD_OID = ATT.PAT_STD_OID
	WHERE 
		PAT_DATE BETWEEN @STARTDATE AND @ENDDATE
		AND ATT.PAT_CODE_VIEW IN ('A', 'A-E')
		AND ATT.PAT_SKL_OID = ST.STD_SKL_OID
	GROUP BY
		ATT.PAT_STD_OID,
		ST.STD_NAME_VIEW) T3
ON 
	(T1.Student = T3.Student)
ORDER BY
	T1.Student