| Value |
 Expression: "
/* Get all the End of Term records that have changed during the input date range */
SELECT
EOT.STDNT_ID AS StudentId
,EOT.TRM_YR AS FirstYearTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 1
ELSE 0
END AS MeetsGPARequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 1
ELSE 0
END AS Meets24CreditsRequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) = 0 THEN 0
WHEN ISNULL(CUM_HRS_ATT,0) = 0 THEN 0
WHEN (CUM_HRS_ERN / CUM_HRS_ATT) * 100 >= 67 THEN 1
ELSE 0
END AS Meets67PctCompletedRequirement
,CASE
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedCollegeExpCourse
,CASE
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedGatewayCourse
,CASE
WHEN Summer.SummerStudentId IS NOT NULL THEN 1
ELSE 0
END AS AttendedSummerTerm
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 0 /* Satisfied GPA */
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 0 /* Sa
/* Get all the End of Term records that have changed during the input date range */
SELECT
EOT.STDNT_ID AS StudentId
,EOT.TRM_YR AS FirstYearTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 1
ELSE 0
END AS MeetsGPARequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 1
ELSE 0
END AS Meets24CreditsRequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) = 0 THEN 0
WHEN ISNULL(CUM_HRS_ATT,0) = 0 THEN 0
WHEN (CUM_HRS_ERN / CUM_HRS_ATT) * 100 >= 67 THEN 1
ELSE 0
END AS Meets67PctCompletedRequirement
,CASE
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedCollegeExpCourse
,CASE
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedGatewayCourse
,CASE
WHEN Summer.SummerStudentId IS NOT NULL THEN 1
ELSE 0
END AS AttendedSummerTerm
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 0 /* Satisfied GPA */
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 0 /* Satisfied Credit Hours */
WHEN ISNULL(CUM_HRS_ATT,0) > 0 AND (ISNULL(CUM_HRS_ERN,0) / CUM_HRS_ATT) * 100 >= 67 THEN 0 /* Satisfied 67% Course Completion/Pass Rate */
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 0 /* Satisfied College Experience Course */
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 0 /* Satisfied Gateway Course */
WHEN Summer.SummerStudentId IS NOT NULL THEN 0 /* Attended Summer Term */
ELSE 1
END AS DidNotMeetAny1stYearSuccessFactors
FROM ST_STDNT_ENDOFTERM_A AS EOT
/* Find the latest (term and seq num) student objective record for the specific student and program. */
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
/* Determine if the student attended a summer term and completed a course within the last year. */
/* If so, then the SummerStudentId field from this join will not be NULL. */
OUTER APPLY (
SELECT TOP 1
STDNT_ID AS SummerStudentId
FROM ST_STDNT_ENDOFTERM_A AS InsideSummer
WHERE InsideSummer.STDNT_ID IS NOT NULL AND InsideSummer.TRM_YR IS NOT NULL
AND InsideSummer.STDNT_ID = EOT.STDNT_ID
AND InsideSummer.TRM_YR > (CAST((CAST(SUBSTRING(EOT.TRM_YR,1,4) AS INT) - 1) AS VARCHAR(4)) + SUBSTRING(EOT.TRM_YR,5,1))
AND InsideSummer.TRM_YR <= EOT.TRM_YR
AND SUBSTRING(InsideSummer.TRM_YR,5,1) IN ('3','4') /* Summer Term */
AND ISNULL(InsideSummer.TRM_HRS_ERN,0) > 0 /* Attended = Completed a course */
) AS Summer |
| Expression |
"
/* Get all the End of Term records that have changed during the input date range */
SELECT
EOT.STDNT_ID AS StudentId
,EOT.TRM_YR AS FirstYearTerm
,PGM_ID AS ProgramId
,ISNULL(PgmObj.StudentObjectiveAK,'') AS StudentObjectiveAK
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 1
ELSE 0
END AS MeetsGPARequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 1
ELSE 0
END AS Meets24CreditsRequirement
,CASE
WHEN ISNULL(CUM_HRS_ERN,0) = 0 THEN 0
WHEN ISNULL(CUM_HRS_ATT,0) = 0 THEN 0
WHEN (CUM_HRS_ERN / CUM_HRS_ATT) * 100 >= 67 THEN 1
ELSE 0
END AS Meets67PctCompletedRequirement
,CASE
WHEN ISNULL(TAKEN_COLLEXPR_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedCollegeExpCourse
,CASE
WHEN ISNULL(TAKEN_GATEWAY_COURSE,'N') = 'Y' THEN 1
ELSE 0
END AS CompletedGatewayCourse
,CASE
WHEN Summer.SummerStudentId IS NOT NULL THEN 1
ELSE 0
END AS AttendedSummerTerm
,CASE
WHEN ISNULL(CUM_GPA,0) >= 2.0 THEN 0 /* Satisfied GPA */
WHEN ISNULL(CUM_HRS_ERN,0) >= 24.0 THEN 0 /* Sa |
Properties
