EnterpriseDW

N/A

If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.

/* 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

" /* 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