Stored Procedure - usp_FactInstitutionalRetentionT2TSnapshot

Property	Value
Name	usp_FactInstitutionalRetentionT2TSnapshot_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name

Data Type

Direction

Description

Name	Type
ST_ACDMC_HIST_A	Table
ST_STDNT_ENDOFTERM_A	Table
ST_STDNT_OBJ_AWD_A	Table

Name	Type
ST_ACDMC_HIST_A	TABLE
ST_STDNT_ENDOFTERM_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactInstitutionalRetentionT2TSnapshot_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactInstitutionalRetentionT2TSnapshot_Select
Title:            Primary Select for the Institutional Retention Cohort Fact (Term To Term)
Date:            10/10/2013
System/Project:    Student Retention Dashboard
Description:    This procedure will build the Fact records for the Term to Term Institutional Retention records.
Revision History:
10/10/2013 Ian Peterson Created.
*/
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_FactInstitutionalRetentionT2TSnapshot_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(9)) AS StudentId
        , CAST('aaa' AS varchar(6)) AS InstitutionalRetentionCohortTerm
        , CAST('aaa' AS varchar(6)) AS InstitutionalRetentionTrackingTerm
        , CAST('aaa' AS varchar(15)) AS RetentionCohortTypeCode
        , CAST(1 as smallint) as RetentionYearCount
        , CAST(1 as smallint) as RetentionTermCount
        , CAST(1 as smallint) as EnrolledInTerm
        , CAST(1 as smallint) as Graduated
        , CAST(1 as smallint) as NotEnrolledInTerm
    END

/* This table will contain all students and terms from the End Of Term table where the student took a credit class. */
CREATE TABLE #term2term_cohort
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[T2TCohortTerm] VARCHAR(6) NOT NULL
    ,[TermPlus1] VARCHAR(6) NOT NULL
    ,[TermPlus2] VARCHAR(6) NOT NULL
    )
INSERT INTO #term2term_cohort
    (
     [StudentId]
    ,[T2TCohortTerm]
    ,[TermPlus1]
    ,[TermPlus2]
    )
    SELECT
          EOT.STDNT_ID AS StudentId
          ,EOT.TRM_YR AS T2TCohortTerm
          ,CASE
            /* Determine the following term. Summer terms are treated as one term. */
            WHEN SUBSTRING(EOT.TRM_YR,5,1) IN ('3','4')
                THEN CAST((CAST(SUBSTRING(EOT.TRM_YR,1,4) AS int) + 1) AS varchar(4)) + '1'
            ELSE SUBSTRING(EOT.TRM_YR,1,4) + CAST((CAST(SUBSTRING(EOT.TRM_YR,5,1) AS int) + 1) AS varchar(1))
           END AS TermPlus1
          ,CASE
            /* Determine the following term. Summer terms are treated as one term. */
            WHEN SUBSTRING(EOT.TRM_YR,5,1) = '2'
                THEN CAST((CAST(SUBSTRING(EOT.TRM_YR,1,4) AS int) + 1) AS varchar(4)) + '1'
            WHEN SUBSTRING(EOT.TRM_YR,5,1) IN ('3','4')
                THEN CAST((CAST(SUBSTRING(EOT.TRM_YR,1,4) AS int) + 1) AS varchar(4)) + '2'
            ELSE SUBSTRING(EOT.TRM_YR,1,4) + CAST((CAST(SUBSTRING(EOT.TRM_YR,5,1) AS int) + 2) AS varchar(1))
           END AS TermPlus2
    FROM ST_STDNT_ENDOFTERM_A EOT
    /* Join on set of students that completed a local credit class in the term. */
    INNER JOIN (
            SELECT
                AH1.STUDENT_ID,
                RTRIM(SUBSTRING(AH1.CLASS_KEY,12,6)) AS AH_TERM
            FROM ST_ACDMC_HIST_A AH1
            WHERE SUBSTRING(AH1.CLASS_KEY,1,1) = 'C'  /* Credit Class */
                AND AH1.SPCL_CRED_TY IS     NULL/* No test classes */
            GROUP BY AH1.STUDENT_ID, SUBSTRING(AH1.CLASS_KEY,12,6)
        ) AH
        ON (
            EOT.STDNT_ID = AH.STUDENT_ID
            AND EOT.TRM_YR = AH.AH_TERM
        )
    WHERE
        /* Only interested in Fall and Spring cohorts. */
        SUBSTRING(EOT.TRM_YR,5,1) IN ('1','2')
SELECT
     EndOfTerm.STDNT_ID AS StudentId
    ,GetCohort.T2TCohortTerm AS InstitutionalRetentionCohortTerm
    ,EndOfTerm.TRM_YR AS InstitutionalRetentionTrackingTerm
    ,CASE
        WHEN SUBSTRING(GetCohort.T2TCohortTerm,5,1) = '1'
            THEN 'FallToSpring'
        WHEN SUBSTRING(GetCohort.T2TCohortTerm,5,1) = '2'
            THEN 'SpringToFall'
        ELSE 'N/A'
        END AS RetentionCohortTypeCode
    ,0 AS RetentionYearCount
    ,CASE
        WHEN EndOfTerm.TRM_YR = GetCohort.T2TCohortTerm THEN 1
        WHEN EndOfTerm.TRM_YR = GetCohort.TermPlus1 THEN 2
        WHEN EndOfTerm.TRM_YR = GetCohort.TermPlus2 THEN 2
        ELSE 0
        END AS RetentionTermCount
    ,CASE
        WHEN StObjAwd.STDNT_ID IS NOT NULL THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'Y' THEN 1
        ELSE 0
        END AS EnrolledInTerm
    ,CASE
        WHEN StObjAwd.STDNT_ID IS NOT NULL THEN 1
        ELSE 0
        END AS Graduated
    ,CASE
        WHEN StObjAwd.STDNT_ID IS NOT NULL THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'N' THEN 1
        ELSE 0
        END AS NotEnrolledInTerm
FROM ST_STDNT_ENDOFTERM_A AS EndOfTerm
/* Inner Join to determine the student's cohort term. */
/* Tracking term must be cohort term or the following term. */
INNER JOIN #term2term_cohort AS GetCohort
    ON (
        EndOfTerm.STDNT_ID = GetCohort.StudentId
        AND (EndOfTerm.TRM_YR = GetCohort.T2TCohortTerm
            OR (EndOfTerm.TRM_YR = GetCohort.TermPlus1 AND SUBSTRING(GetCohort.T2TCohortTerm,5,1) = '1')
            OR (EndOfTerm.TRM_YR = GetCohort.TermPlus2 AND SUBSTRING(GetCohort.T2TCohortTerm,5,1) = '2'))
    )
/* Join with the objective that the student graduated in the tracking term. */
/* If none exist for the tracking term, then the student did not graduate that term. */
LEFT OUTER JOIN
    (
        SELECT STDNT_ID, ACT_GRAD_TRM
        FROM ST_STDNT_OBJ_AWD_A
        WHERE STDNT_ID IS NOT NULL AND PGM_ID IS NOT NULL AND EFF_TERM IS NOT NULL AND ACT_GRAD_TRM IS NOT NULL
        GROUP BY STDNT_ID, ACT_GRAD_TRM
    ) AS StObjAwd
    ON (
        EndOfTerm.STDNT_ID = StObjAwd.STDNT_ID
        AND EndOfTerm.TRM_YR = StObjAwd.ACT_GRAD_TRM
    )