Support

Fixed - As of the 5/17/2019 update, when inactivating or transferring a student with summer school course requests, users now have the option to drop the summer school course requests. 

Transferred students with Summer School Course Request (SSR) records may be taking a seat in the summer school before the student was inactivated.  Once a student is inactivated, the scheduler does not schedule them unless the Scheduling option to "Include Inactive Students" is selected.  

We will be adding a warning when Inactivating a student with existing  Summer School Course Request (SSR) records.  The existing SSR records will display and can be dropped at that time.  We will also display existing SSR records when enrolling students.  These features will be released soon.

The following SQL queries will help identify these inactive students with SSR records.  Please review the results and delete any  Summer School Course Request (SSR) records for students who will not be attending summer school.

--Show summer school course requests for inactive students not active or pre-enrolled anywhere

SELECT STU.SC, STU.ID, STU.FN, STU.LN, STU.GR, STU.TG, SSR.SSC, SSR.SE, CRS.CN, CRS.CO

FROM STU

LEFT JOIN SSR ON STU.ID = SSR.ID AND SSR.DEL = 0 AND STU.DEL = 0

LEFT JOIN CRS ON CRS.CN = SSR.CN

WHERE SSR.CN <> ''

AND STU.ID NOT IN (SELECT ID FROM STU WHERE DEL = 0 AND TG IN ('*',''))

AND STU.SC IN (SELECT DISTINCT SC FROM STU WHERE DEL = 0 AND TG IN ('*','')) --Only include schools where an active or pre-enrolled student exists (Hopefully your inactive/archive school is omitted by this)

--Show active or pre-enrolled students having summer school course requests not at the most popular places their peers at their school are going for summer

SELECT STU.SC, STU.ID, STU.FN, STU.LN, STU.GR, STU.TG, SSR.SSC, CRS.CN, CRS.CO

,PopularSS.PopularSummerSchool_1, PopularSS.PopularSummerSchool_1_ReqCounts

,PopularSS.PopularSummerSchool_2, PopularSS.PopularSummerSchool_2_ReqCounts

,PopularSS.PopularSummerSchool_3, PopularSS.PopularSummerSchool_3_ReqCounts

FROM STU

LEFT JOIN SSR ON STU.ID = SSR.ID AND SSR.DEL = 0 AND STU.DEL = 0 

LEFT JOIN CRS ON CRS.CN = SSR.CN

LEFT JOIN (

SELECT SC

,MAX(CASE WHEN Ranked = 1 then ssc else '' end) PopularSummerSchool_1

,MAX(CASE WHEN Ranked = 1 then Counts else '' end) PopularSummerSchool_1_ReqCounts

,MAX(CASE WHEN Ranked = 2 then ssc else '' end) PopularSummerSchool_2

,MAX(CASE WHEN Ranked = 2 then Counts else '' end) PopularSummerSchool_2_ReqCounts

,MAX(CASE WHEN Ranked = 3 then ssc else '' end) PopularSummerSchool_3

,MAX(CASE WHEN Ranked = 3 then Counts else '' end) PopularSummerSchool_3_ReqCounts

FROM (

SELECT STU.SC, SSR.SSC, COUNT(SSR.SSC) COUNTS, ROW_NUMBER() OVER (PARTITION BY SC ORDER BY SC, COUNT(SSR.SSC) DESC) Ranked

FROM STU INNER JOIN SSR ON STU.ID = SSR.ID AND STU.DEL = 0 AND SSR.DEL = 0

WHERE STU.TG IN ('*','')

GROUP BY STU.SC, SSR.SSC

HAVING COUNT(SSR.SSC) > 2 --Specify how many course requests is usual for a small portion of the school attending a destination summer school. This helps omit outliers.

) A

GROUP BY SC ) PopularSS

ON STU.SC = PopularSS.SC

WHERE SSR.CN <> ''

AND SSR.SSC <> PopularSummerSchool_1 --not in most popular summer school destination for students at their school

AND SSR.SSC not in (PopularSummerSchool_1, PopularSummerSchool_2) --not in the 2 most popular summer school destinations for students at their school

AND SSR.SSC not in (PopularSummerSchool_1, PopularSummerSchool_2,PopularSummerSchool_3) --not in the 3 most popular summer school destinations for students at their school

ORDER BY STU.LN, STU.FN, STU.SC