Stored Procedure - usp_FactInstitutionalRetentionT2TSnapshot

Property	Value
Name	usp_FactInstitutionalRetentionT2TSnapshot_Select2
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_Select2] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactInstitutionalRetentionT2TSnapshot_Select2
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.
12/11/2013 Harriett Stoddard Added Graduation Check for Summer Term '4'
01/08/2014 Harriett Stoddard Added Check for Enrollment in Summer Term '3' or Summer Term '4'
01/29/2014 Harriett Stoddard Removed '2' from the WHERE clause for ST_STDNT_OBJ_AWD_A
*/
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_FactInstitutionalRetentionT2TSnapshot_Select2]
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 #spring2summer_cohort
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[T2TCohortTerm] VARCHAR(6) NOT NULL
    ,[TermPlus1] VARCHAR(6) NOT NULL
    ,[TermPlus2] VARCHAR(6) NOT NULL
    ,[EnrolledInSummer] VARCHAR(1) NOT NULL
    )
INSERT INTO #spring2summer_cohort
    (
     [StudentId]
    ,[T2TCohortTerm]
    ,[TermPlus1]
    ,[TermPlus2]
    ,[EnrolledInSummer]
    )
    SELECT
          EOT.STDNT_ID AS StudentId
          ,EOT.TRM_YR AS T2TCohortTerm
          ,SUBSTRING(EOT.TRM_YR,1,4) + CAST((CAST(SUBSTRING(EOT.TRM_YR,5,1) AS int) + 1) AS varchar(1)) AS TermPlus1
          ,SUBSTRING(EOT.TRM_YR,1,4) + CAST((CAST(SUBSTRING(EOT.TRM_YR,5,1) AS int) + 2) AS varchar(1)) AS TermPlus2
          ,CASE
                WHEN Summer1.STDNT_ENROLLED_FLG = 'Y' THEN 'Y'
                WHEN Summer2.STDNT_ENROLLED_FLG = 'Y' THEN 'Y'
                ELSE 'N'
                END AS EnrolledInSummer

    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
        )

    /* Join for Enrollment for Summer 1 */
    LEFT OUTER JOIN ST_STDNT_ENDOFTERM_A AS Summer1
    ON (EOT.STDNT_ID = Summer1.STDNT_ID
        AND SUBSTRING(EOT.TRM_YR,1,4) = SUBSTRING(Summer1.TRM_YR,1,4)
        AND SUBSTRING(Summer1.TRM_YR,5,1) = '3')

    /* Join for Enrollment for Summer 2 */
    LEFT OUTER JOIN ST_STDNT_ENDOFTERM_A AS Summer2
    ON (EOT.STDNT_ID = Summer2.STDNT_ID
        AND SUBSTRING(EOT.TRM_YR,1,4) = SUBSTRING(Summer2.TRM_YR,1,4)
        AND SUBSTRING(Summer2.TRM_YR,5,1) = '4')

    WHERE
        /* Only interested in Spring cohorts. */
        SUBSTRING(EOT.TRM_YR,5,1) = '2'
SELECT
     EndOfTerm.STDNT_ID AS StudentId
    ,GetCohort.T2TCohortTerm AS InstitutionalRetentionCohortTerm
    ,EndOfTerm.TRM_YR AS InstitutionalRetentionTrackingTerm
    ,'SpringToSummer' AS RetentionCohortTypeCode
    ,0 AS RetentionYearCount
    ,CASE
        WHEN EndOfTerm.TRM_YR = GetCohort.T2TCohortTerm THEN 1
        WHEN EndOfTerm.TRM_YR = GetCohort.TermPlus1 THEN 2
        ELSE 0
        END AS RetentionTermCount
    ,CASE
        WHEN StObjAwd.STDNT_ID IS NOT NULL THEN 0
        WHEN EndOfTerm.TRM_YR = GetCohort.T2TCohortTerm AND EndOfTerm.STDNT_ENROLLED_FLG = 'Y' THEN 1
        WHEN EndOfTerm.TRM_YR = GetCohort.TermPlus1 AND GetCohort.EnrolledInSummer = '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.TRM_YR = GetCohort.T2TCohortTerm AND EndOfTerm.STDNT_ENROLLED_FLG = 'N' THEN 1
        WHEN EndOfTerm.TRM_YR = GetCohort.TermPlus1 AND GetCohort.EnrolledInSummer = '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 #spring2summer_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) = '2'))  /* Spring To Summer */
    )
/* 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
    (
            SELECTSTDNT_ID
                ,ACT_GRAD_TRM
                ,ROW_NUMBER() OVER (PARTITION BY STDNT_ID, SUBSTRING(ACT_GRAD_TRM,1,4)
                                    ORDER BY SUBSTRING(ACT_GRAD_TRM,5,2)) AS rn
        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
            AND SUBSTRING(ACT_GRAD_TRM,5,1) IN ('3','4')
        GROUP BY STDNT_ID, ACT_GRAD_TRM

    ) AS StObjAwd
    ON (
        EndOfTerm.STDNT_ID = StObjAwd.STDNT_ID
        AND
        (
        (EndOfTerm.TRM_YR = StObjAwd.ACT_GRAD_TRM)
        OR (StObjAwd.ACT_GRAD_TRM = GetCohort.TermPlus2 AND SUBSTRING(EndOfTerm.TRM_YR,5,1) = '3')
        )
        AND rn = 1
    )