~[tlist_sql;
SELECT DISTINCT Enroll.Name AS School, Enroll.School_Number AS School_Number,
SUM ( CASE WHEN Enroll.Grade_Level = -3 THEN 1 ELSE NULL END ) AS Minus_Three,
SUM ( CASE WHEN Enroll.Grade_Level = -2 THEN 1 ELSE NULL END ) AS Minus_Two,
SUM ( CASE WHEN Enroll.Grade_Level = -1 THEN 1 ELSE NULL END ) AS Minus_One,
SUM ( CASE WHEN Enroll.Grade_Level = 0 THEN 1 ELSE NULL END ) AS K,
SUM ( CASE WHEN Enroll.Grade_Level = 1 THEN 1 ELSE NULL END ) AS One,
SUM ( CASE WHEN Enroll.Grade_Level = 2 THEN 1 ELSE NULL END ) AS Two,
SUM ( CASE WHEN Enroll.Grade_Level = 3 THEN 1 ELSE NULL END ) AS Three,
SUM ( CASE WHEN Enroll.Grade_Level = 4 THEN 1 ELSE NULL END ) AS Four,
SUM ( CASE WHEN Enroll.Grade_Level = 5 THEN 1 ELSE NULL END ) AS Five,
SUM ( CASE WHEN Enroll.Grade_Level = 6 THEN 1 ELSE NULL END ) AS Six,
SUM ( CASE WHEN Enroll.Grade_Level = 7 THEN 1 ELSE NULL END ) AS Seven,
SUM ( CASE WHEN Enroll.Grade_Level = 8 THEN 1 ELSE NULL END ) AS Eight,
SUM ( CASE WHEN Enroll.Grade_Level = 9 THEN 1 ELSE NULL END ) AS Nine,
SUM ( CASE WHEN Enroll.Grade_Level = 10 THEN 1 ELSE NULL END ) AS Ten,
SUM ( CASE WHEN Enroll.Grade_Level = 11 THEN 1 ELSE NULL END ) AS Eleven,
SUM ( CASE WHEN Enroll.Grade_Level = 12 THEN 1 ELSE NULL END ) AS Twelve,
SUM ( CASE WHEN Enroll.Grade_Level = 13 THEN 1 ELSE NULL END ) AS Thirteen,
SUM ( CASE WHEN Enroll.Grade_Level < 99 THEN 1 ELSE NULL END ) AS Total,
Enroll.SortOrder
FROM (
SELECT Schools.Name AS Name, Schools.School_Number AS School_Number, Students.Grade_Level AS Grade_Level, Schools.SortOrder AS SortOrder
FROM Students
INNER JOIN Schools
ON Schools.School_Number = Students.SchoolID
WHERE Students.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND Students.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND Students.Grade_Level < 99
UNION ALL
SELECT Schools.Name AS Name, Schools.School_Number AS School_Number, ReEnrollments.Grade_Level AS Grade_Level, Schools.SortOrder AS SortOrder
FROM ReEnrollments
INNER JOIN Schools
ON Schools.School_Number = ReEnrollments.SchoolID
WHERE ReEnrollments.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND ReEnrollments.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND ReEnrollments.Grade_Level < 99) Enroll
GROUP BY Enroll.Name, Enroll.School_Number, Enroll.SortOrder
ORDER BY Enroll.SortOrder;alternatecolor]
[/tlist_sql]
~[tlist_sql;
SELECT 'Total' AS School,
SUM ( CASE WHEN Grade_Level = -3 THEN 1 ELSE NULL END ) AS Minus_Three,
SUM ( CASE WHEN Grade_Level = -2 THEN 1 ELSE NULL END ) AS Minus_Two,
SUM ( CASE WHEN Grade_Level = -1 THEN 1 ELSE NULL END ) AS Minus_One,
SUM ( CASE WHEN Grade_Level = 0 THEN 1 ELSE NULL END ) AS K,
SUM ( CASE WHEN Grade_Level = 1 THEN 1 ELSE NULL END ) AS One,
SUM ( CASE WHEN Grade_Level = 2 THEN 1 ELSE NULL END ) AS Two,
SUM ( CASE WHEN Grade_Level = 3 THEN 1 ELSE NULL END ) AS Three,
SUM ( CASE WHEN Grade_Level = 4 THEN 1 ELSE NULL END ) AS Four,
SUM ( CASE WHEN Grade_Level = 5 THEN 1 ELSE NULL END ) AS Five,
SUM ( CASE WHEN Grade_Level = 6 THEN 1 ELSE NULL END ) AS Six,
SUM ( CASE WHEN Grade_Level = 7 THEN 1 ELSE NULL END ) AS Seven,
SUM ( CASE WHEN Grade_Level = 8 THEN 1 ELSE NULL END ) AS Eight,
SUM ( CASE WHEN Grade_Level = 9 THEN 1 ELSE NULL END ) AS Nine,
SUM ( CASE WHEN Grade_Level = 10 THEN 1 ELSE NULL END ) AS Ten,
SUM ( CASE WHEN Grade_Level = 11 THEN 1 ELSE NULL END ) AS Eleven,
SUM ( CASE WHEN Grade_Level = 12 THEN 1 ELSE NULL END ) AS Twelve,
SUM ( CASE WHEN Grade_Level = 13 THEN 1 ELSE NULL END ) AS Thirteen,
SUM ( CASE WHEN Grade_Level < 99 THEN 1 ELSE NULL END ) AS Total
FROM (
SELECT Grade_Level
FROM Students
WHERE Students.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND Students.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND Students.Grade_Level < 99
UNION ALL
SELECT Grade_Level
FROM ReEnrollments
WHERE ReEnrollments.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND ReEnrollments.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND ReEnrollments.Grade_Level < 99) Enroll]
~(School)
~(Minus_Three)
~(Minus_Two)
~(Minus_one)
~(K)
~(One)
~(Two)
~(Three)
~(Four)
~(Five)
~(Six)
~(Seven)
~(Eight)
~(Nine)
~(Ten)
~(Eleven)
~(Twelve)
~(Thirteen)
~(Total)
[/tlist_sql]
School
FTE Name
Pre-Preschool
Preschool
Pre-K
K
1
2
3
4
5
6
7
8
9
10
11
12
13
Total
~[tlist_sql;
SELECT DISTINCT Enroll.Name AS School, Enroll.FTEName AS FTEName, Enroll.School_Number AS School_Number, Enroll.FTEID AS FTEID,
SUM ( CASE WHEN Enroll.Grade_Level = -3 THEN 1 ELSE NULL END ) AS Minus_Three,
SUM ( CASE WHEN Enroll.Grade_Level = -2 THEN 1 ELSE NULL END ) AS Minus_Two,
SUM ( CASE WHEN Enroll.Grade_Level = -1 THEN 1 ELSE NULL END ) AS Minus_One,
SUM ( CASE WHEN Enroll.Grade_Level = 0 THEN 1 ELSE NULL END ) AS K,
SUM ( CASE WHEN Enroll.Grade_Level = 1 THEN 1 ELSE NULL END ) AS One,
SUM ( CASE WHEN Enroll.Grade_Level = 2 THEN 1 ELSE NULL END ) AS Two,
SUM ( CASE WHEN Enroll.Grade_Level = 3 THEN 1 ELSE NULL END ) AS Three,
SUM ( CASE WHEN Enroll.Grade_Level = 4 THEN 1 ELSE NULL END ) AS Four,
SUM ( CASE WHEN Enroll.Grade_Level = 5 THEN 1 ELSE NULL END ) AS Five,
SUM ( CASE WHEN Enroll.Grade_Level = 6 THEN 1 ELSE NULL END ) AS Six,
SUM ( CASE WHEN Enroll.Grade_Level = 7 THEN 1 ELSE NULL END ) AS Seven,
SUM ( CASE WHEN Enroll.Grade_Level = 8 THEN 1 ELSE NULL END ) AS Eight,
SUM ( CASE WHEN Enroll.Grade_Level = 9 THEN 1 ELSE NULL END ) AS Nine,
SUM ( CASE WHEN Enroll.Grade_Level = 10 THEN 1 ELSE NULL END ) AS Ten,
SUM ( CASE WHEN Enroll.Grade_Level = 11 THEN 1 ELSE NULL END ) AS Eleven,
SUM ( CASE WHEN Enroll.Grade_Level = 12 THEN 1 ELSE NULL END ) AS Twelve,
SUM ( CASE WHEN Enroll.Grade_Level = 13 THEN 1 ELSE NULL END ) AS Thirteen,
SUM ( CASE WHEN Enroll.Grade_Level < 99 THEN 1 ELSE NULL END ) AS Total,
Enroll.SortOrder
FROM (
SELECT Schools.Name AS Name, Schools.School_Number AS School_Number, Students.Grade_Level AS Grade_Level, Schools.SortOrder AS SortOrder, Students.FTEID AS FTEID, FTE.Name AS FTEName
FROM Students
INNER JOIN Schools
ON Schools.School_Number = Students.SchoolID
INNER JOIN FTE
ON Students.FTEID = FTE.ID
WHERE Students.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND Students.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' )
UNION ALL
SELECT Schools.Name AS Name, Schools.School_Number AS School_Number, ReEnrollments.Grade_Level AS Grade_Level, Schools.SortOrder AS SortOrder, ReEnrollments.FTEID AS FTEID, FTE.Name AS FTEName
FROM ReEnrollments
INNER JOIN Schools
ON Schools.School_Number = ReEnrollments.SchoolID
INNER JOIN FTE
ON ReEnrollments.FTEID = FTE.ID
WHERE ReEnrollments.EntryDate <= TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) AND ReEnrollments.ExitDate > TO_DATE ( '~[gpv:date]', 'MM/DD/YYYY' ) ) Enroll
GROUP BY Enroll.Name, Enroll.School_Number, Enroll.FTEID, Enroll.FTEName, Enroll.SortOrder
ORDER BY Enroll.SortOrder, Enroll.FTEID;alternatecolor]