Known Issue - Summer School Transfer Students - Fixed 5/17/2019
Kathy Kelly
started a topic
about 4 years ago
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
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
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
Kathy Kelly
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
1 person likes this