|
/* Get all the End of Term records that have changed during the input date range */
SELECT
STDNT_ID AS StudentId
,TRM_YR AS TrackingTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,ISNULL(CUM_GPA,0) AS CumGPA
,ISNULL(CUM_GRD_PTS,0) AS CumGradePoints
,ISNULL(CUM_HRS_ERN,0) AS CumHoursEarned
,ISNULL(CUM_HRS_ATT,0) AS CumHoursAttempted
,ISNULL(CUM_GPA_HRS,0) AS CumGPAHours
,ISNULL(TRM_GPA,0) AS TermGPA
,ISNULL(TRM_GRD_PTS,0) AS TermGradePoints
,ISNULL(TRM_HRS_ERN,0) AS TermHoursEarned
,ISNULL(TRM_HRS_ATT,0) AS TermHoursAttempted
,ISNULL(TRM_GPA_HRS,0) AS TermGPAHours
,CASE
WHEN STDNT_ENROLLED_FLG = 'Y' THEN 1
ELSE 0
END AS EnrolledInTerm
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 1
ELSE 0
END AS GPA_None
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 0 /* No GPA */
WHEN ISNULL(CUM_GPA,0) < 2.0 THEN 1
ELSE 0
END AS GPA_000_199
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.0 AND 2.49 THEN 1
ELSE 0
END AS GPA_200_249
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.5 AND 2.99 THEN 1
ELSE 0
END AS GPA_250_299
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 3.0 AND 3.49 THEN 1
ELSE 0
END AS GPA_300_349
,CASE
WHEN ISNULL(CUM_GPA,0) >= 3.5 THEN 1
ELSE 0
END AS GPA_350_Plus
,CASE
WHEN DEGREE_LT25PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_LessThan25_Pct
,CASE
WHEN DEGREE_25PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_25_Pct
,CASE
WHEN DEGREE_50PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_50_Pct
,CASE
WHEN DEGREE_75PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_75_Pct
,CASE
WHEN DEGREE_100PCT_CMPLTD = 'Y' THEN 1
ELSE 0
END AS DegreeBenchmark_100_Pct
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'C' THEN 1
ELSE 0
END AS SOAP_Clear
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'W' THEN 1
ELSE 0
END AS SOAP_Warning
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'A' THEN 1
ELSE 0
END AS SOAP_Appeal
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'S' THEN 1
ELSE 0
END AS SOAP_Suspension
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'P' THEN 1
ELSE 0
END AS SOAP_Probation
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) = 'D' THEN 1
ELSE 0
END AS SOAP_Dismissal
,CASE
WHEN SUBSTRING(ACDMC_STAT_CD,1,1) NOT IN ('C','W','A','S','P','D') THEN 1
ELSE 0
END AS SOAP_Other
,CASE
WHEN STDNT_GRAD_PROGRESS = 'NE' THEN 1
ELSE 0
END AS GraduationProgress_NotEnrolledFor2Terms
,CASE
WHEN STDNT_GRAD_PROGRESS = 'EN' THEN 1
ELSE 0
END AS GraduationProgress_Continueing
,CASE
WHEN STDNT_GRAD_PROGRESS = 'AG' THEN 1
ELSE 0
END AS GraduationProgress_AppliedToGraduate
,CASE
WHEN STDNT_GRAD_PROGRESS = 'GR' THEN 1
ELSE 0
END AS GraduationProgress_Graduated
FROM ST_STDNT_ENDOFTERM_A AS EOT
OUTER APPLY (
SELECT TOP 1
(STDNT_ID + PGM_ID + EFF_TERM + RIGHT('000' + CAST(SEQ_NUM AS VARCHAR(3)), 3)) AS StudentObjectiveAK
FROM ST_STDNT_OBJ_AWD_A Obj
WHERE Obj.STDNT_ID IS NOT NULL AND Obj.PGM_ID IS NOT NULL AND Obj.EFF_TERM IS NOT NULL
AND Obj.STDNT_ID = EOT.STDNT_ID
AND Obj.PGM_ID = EOT.PGM_ID
AND Obj.EFF_TERM <= EOT.TRM_YR
ORDER BY Obj.EFF_TERM DESC, Obj.SEQ_NUM DESC
) PgmObj
|
"
/* Get all the End of Term records that have changed during the input date range */
SELECT
STDNT_ID AS StudentId
,TRM_YR AS TrackingTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,ISNULL(CUM_GPA,0) AS CumGPA
,ISNULL(CUM_GRD_PTS,0) AS CumGradePoints
,ISNULL(CUM_HRS_ERN,0) AS CumHoursEarned
,ISNULL(CUM_HRS_ATT,0) AS CumHoursAttempted
,ISNULL(CUM_GPA_HRS,0) AS CumGPAHours
,ISNULL(TRM_GPA,0) AS TermGPA
,ISNULL(TRM_GRD_PTS,0) AS TermGradePoints
,ISNULL(TRM_HRS_ERN,0) AS TermHoursEarned
,ISNULL(TRM_HRS_ATT,0) AS TermHoursAttempted
,ISNULL(TRM_GPA_HRS,0) AS TermGPAHours
,CASE
WHEN STDNT_ENROLLED_FLG = 'Y' THEN 1
ELSE 0
END AS EnrolledInTerm
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 1
ELSE 0
END AS GPA_None
,CASE
WHEN ISNULL(CUM_GPA,0) = 0 AND ISNULL(CUM_HRS_ATT,0) = 0 THEN 0 /* No GPA */
WHEN ISNULL(CUM_GPA,0) < 2.0 THEN 1
ELSE 0
END AS GPA_000_199
,CASE
WHEN ISNULL(CUM_GPA,0) BETWEEN 2.0 AND 2.49 THEN 1
EL |
|
User Defined Variables
