Testing Phase
Import File and Data Validation
...
Object: StoredProcedure [SPS].[dbo].[load_ept_table]
Line 271:
SUM(CASE SUBSTRING(ac.Manual_Decision_Code,1,1) when 'A' THEN 1 ELSE 0 END) Admits,
Suggested Modification:
SUM (CASE WHEN ((SUBSTRING(app.[Manual_Decision_Code],1,1)='A') -- normal Admits
OR app.[Manual_Decision_Code]='DF') -- count DF (Admit to Future) as an admit in the applying term
AND (app.[Decision_Reply_Code]<>'D' OR app.[Decision_Reply_Code] IS NULL) -- ignore duplicate deferral apps (old method)
THEN 1 else 0 END) Admits,
Issue: Headcount totals do not align
Known issues that are causing the difference between methodologies:
SPAAMS 20183 (3 PIDs)
- TSTA table is not fully aligned with PROD in the DEV environment.
- Updating an individual record in SPAAMS aligned it on both sides. (C004161335)
- SPS EPT code strips away cases where the 'NW' entering term has zero dollars in tuition, effectively stripping that person from all cohorts.
- Updating the SPS procedure aligned some of the remaining records. (C004181696 and C004181766)
SPAAMS 20193 (2 PIDs)
- One PID was aligned by zero tuition fix
- SPS EPT code requires a 'NW' term in the program being pulled, the outlier started in SPAMSF then switched to SPAAMS, stripping the person from the cohorts for SPAAMS.
- Need to either changed the way the student is coded, or modify the logic to account for these cases. (C004260275)
- EBIS code is correctly seeing this person in the SPAAMS cohort because the code is not run per program, but for all at once, and doesn't try to pin it into a particular cohort based on the NW status.
SPAAMS 20213 (5 PIDs)
- Two PIDs were aligned by zero tuition fix
- SPS EPT code requires a 'NW' term in the program being pulled, the outlier started in SPAAMO then switched to SPAAMS, stripping the person from the cohorts for SPAAMS.
- Need to either changed the way the students are coded, or modify the logic to account for these cases. (C004423432, C004441389, C004442193)
- EBIS code is correctly seeing this person in the SPAAMS cohort because the code is not run per program, but for all at once, and doesn't try to pin it into a particular cohort based on the NW status
Issue: Headcount and Credit totals do not align with programs that have modality switch cases
Methodology:
- SPRSKM Program (deep dive)
- EBIS Procedure
- 2464 total PIDs in program
- 9 switched into SPRSKM
- EPT Procedure
- 2472 total PIDs in program
- 9 switched into SPRSKM
- 13 switched out of SPRSKM
EBIS student_cohorts_stg = All that started in a cohort, or were modality switched into a cohort, based on U_46 style grouping (but not U_46)
C004638704 = NOT in the U_46 view as SPRSKM, IS in the EPT tuition view