Stored Procedure - usp_FactProgramRetentionSnapshot

Property	Value
Name	usp_FactProgramRetentionSnapshot_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
ST_STDNT_TERM_A	Table
ST_TERM_A	Table
UTL_CODE_TABLE	Table

Name	Type
UTL_CODE_TABLE	TABLE
ST_ACDMC_HIST_A	TABLE
ST_STDNT_ENDOFTERM_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE
ST_STDNT_TERM_A	TABLE
ST_TERM_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_FactProgramRetentionSnapshot_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactProgramRetentionSnapshot_Select
Title:            Primary Select for the Program Retention Cohort Fact
Date:            10/10/2013
System/Project:    Student Retention Dashboard
Description:    This procedure will build the Fact records for the Program Retention records.
Revision History:
10/24/2013 Ian Peterson Created.
*/
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_FactProgramRetentionSnapshot_Select]
AS
/* Determine how many summer terms the college has. */
DECLARE @SummerTerms numeric
SET @SummerTerms = (SELECT CAST(SUBSTRING(ISNULL(DESCRIPTION,'1'),1,1) AS NUMERIC) FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'SITE-PARAM'
            AND CODE = 'SUMMERTRMS'
            AND [STATUS] = 'A')
IF @SummerTerms IS NULL
BEGIN
    SET @SummerTerms =     1/* Default to 1 summer term if table does not exist. */
END
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
          CAST('aaa' AS varchar(9)) AS StudentId
        , CAST('aaa' AS varchar(6)) AS ProgramRetentionCohortTerm
        , CAST('aaa' AS varchar(6)) AS ProgramRetentionTrackingTerm
        , CAST('aaa' AS varchar(20)) AS ProgramRetentionCohortAK
        , CAST(1 as smallint) as TrackingYearsAfterCohortTerm
        , CAST(1 as smallint) as RetentionTermCount
        , CAST(1 as smallint) as EnrolledInTerm
        , CAST(1 as smallint) as Graduated
        , CAST(1 as smallint) as SwitchedPrograms
        , CAST(1 as smallint) as NotEnrolledInTerm
    END

/* This table will contain all students from the End Of Term table and their real cohort terms. */
/* This table will contain all students and their term objectives from the End Of Term table and their program cohort terms. */
CREATE TABLE #student_program_cohort_ftemp
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[ProgramCode] VARCHAR(5) NOT NULL
    ,[CohortTerm] VARCHAR(6) NOT NULL
    )
;WITH StdntTerm AS
    (
    SELECT STDNT_ID, TRM_YR, PGM_ID,
        rn = ROW_NUMBER() OVER (PARTITION BY STDNT_ID ORDER BY TRM_YR)
    FROM ST_STDNT_TERM_A
    LEFT OUTER JOIN (
            SELECT
                STUDENT_ID,
                RTRIM(SUBSTRING(CLASS_KEY,12,6)) AS AH_TERM
            FROM ST_ACDMC_HIST_A
            WHERE SUBSTRING(CLASS_KEY,1,1) = 'C'  /* Credit Class */
                AND SPCL_CRED_TY IS     NULL/* No test classes */
            GROUP BY STUDENT_ID, SUBSTRING(CLASS_KEY,12,6)
            ) AH
        ON (
            ST_STDNT_TERM_A.STDNT_ID = AH.STUDENT_ID
            and ST_STDNT_TERM_A.TRM_YR = AH.AH_TERM
            )
    WHERE AH.STUDENT_ID IS NOT         NULL/* Successful join - completed credit class in term */
        AND ST_STDNT_TERM_A.PGM_ID IS NOT NULL
    )
INSERT INTO #student_program_cohort_ftemp
    (
     [StudentId]
    ,[ProgramCode]
    ,[CohortTerm]
    )
    SELECT
        StdntTerm.STDNT_ID AS StudentId
        ,StdntTerm.PGM_ID AS ProgramCode
        ,StdntTerm.TRM_YR AS CohortTerm
    FROM StdntTerm
    LEFT OUTER JOIN StdntTerm AS y
        ON (
            StdntTerm.STDNT_ID = y.STDNT_ID
            AND StdntTerm.rn = y.rn + 1
            AND StdntTerm.PGM_ID <> y.PGM_ID
            )
    WHERE StdntTerm.STDNT_ID IS NOT NULL AND StdntTerm.TRM_YR IS NOT NULL AND StdntTerm.PGM_ID IS NOT NULL
        AND (y.PGM_ID IS NOT             NULL/* Successful join - program was switched */
            OR StdntTerm.rn = 1)            /* Regardless of join - get first program for each student */
/* For each of the program cohorts, track them for 13 terms (4 years). */
SELECT
    Cohort.StudentId
    ,Cohort.CohortTerm AS ProgramRetentionCohortTerm
    ,Term.TRM_YR AS ProgramRetentionTrackingTerm
    ,Cohort.StudentId + LEFT(Cohort.ProgramCode + ' ',5) + Cohort.CohortTerm AS ProgramRetentionCohortAK
    ,CASE
        WHEN Term.TRM_YR = Cohort.CohortTerm THEN     0/* tracking term is cohort term */
        WHEN SUBSTRING(Term.TRM_YR,5,1) <> SUBSTRING(Cohort.CohortTerm,5,1) THEN -1 /* not full year(s) after cohort term */
        ELSE CAST(LEFT(Term.TRM_YR,4) AS INT) - CAST(LEFT(Cohort.CohortTerm,4) AS INT)
        END AS TrackingYearsAfterCohortTerm
    ,(
        (CAST(LEFT(Term.TRM_YR,4) AS INT) * (2 + @SummerTerms) + CAST(SUBSTRING(Term.TRM_YR,5,1) AS INT))
        -
        (CAST(LEFT(Cohort.CohortTerm,4) AS INT) * (2 + @SummerTerms) + CAST(SUBSTRING(Cohort.CohortTerm,5,1) AS INT))
        + 1
    ) AS RetentionTermCount
    ,CASE
        WHEN StObjAwd.ACT_GRAD_TRM IS NOT NULL
                AND LTRIM(RTRIM(StObjAwd.ACT_GRAD_TRM)) <> ''
                AND EndOfTerm.TRM_YR >= StObjAwd.ACT_GRAD_TRM
            THEN 0
        WHEN EndOfTerm.PGM_ID <> Cohort.ProgramCode THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'Y' THEN 1
        ELSE 0
        END AS EnrolledInTerm
    ,CASE
        WHEN StObjAwd.ACT_GRAD_TRM IS NOT NULL
                AND LTRIM(RTRIM(StObjAwd.ACT_GRAD_TRM)) <> ''
                AND EndOfTerm.TRM_YR >= StObjAwd.ACT_GRAD_TRM
            THEN 1
        ELSE 0
        END AS Graduated
    ,CASE
        WHEN StObjAwd.ACT_GRAD_TRM IS NOT NULL
                AND LTRIM(RTRIM(StObjAwd.ACT_GRAD_TRM)) <> ''
                AND EndOfTerm.TRM_YR >= StObjAwd.ACT_GRAD_TRM
            THEN 0
        WHEN EndOfTerm.PGM_ID = Cohort.ProgramCode THEN 0
        ELSE 1
        END AS SwitchedPrograms
    ,CASE
        WHEN StObjAwd.ACT_GRAD_TRM IS NOT NULL
                AND LTRIM(RTRIM(StObjAwd.ACT_GRAD_TRM)) <> ''
                AND EndOfTerm.TRM_YR >= StObjAwd.ACT_GRAD_TRM
            THEN 0
        WHEN EndOfTerm.PGM_ID <> Cohort.ProgramCode THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'N' THEN 1
        ELSE 0
        END AS NotEnrolledInTerm
FROM #student_program_cohort_ftemp AS Cohort
/* Only interested in students where Cohort Term exists on End Of Term file */
INNER JOIN
    (SELECT STDNT_ID, TRM_YR FROM ST_STDNT_ENDOFTERM_A WHERE STDNT_ID IS NOT NULL) AS EndOfTermExists
    ON (
        Cohort.StudentId = EndOfTermExists.STDNT_ID
        AND Cohort.CohortTerm  = EndOfTermExists.TRM_YR
    )
/* Get the list of Tracking Terms we want. This is up to 4 years past cohort term. */
LEFT OUTER JOIN
    (SELECT TRM_YR FROM ST_TERM_A WHERE TRM_YR IS NOT NULL) AS Term
    ON (
        Term.TRM_YR >= Cohort.CohortTerm
        AND Term.TRM_YR <= (CAST((CAST(LEFT(Cohort.CohortTerm,4) AS INT) + 4) AS VARCHAR(4)) + SUBSTRING(Cohort.CohortTerm,5,1))
    )

/* Only interested if the tracking term exists on the End Of Term file */
INNER JOIN ST_STDNT_ENDOFTERM_A AS EndOfTerm
    ON (
        Cohort.StudentId = EndOfTerm.STDNT_ID
        AND Term.TRM_YR  = EndOfTerm.TRM_YR
    )
/* Join on the program objective record to see if and when student graduated. */
LEFT OUTER JOIN
    (
        SELECT STDNT_ID, PGM_ID, EFF_TERM, 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
    ) AS StObjAwd
    ON (
        Cohort.StudentId = StObjAwd.STDNT_ID
        AND Cohort.ProgramCode = StObjAwd.PGM_ID
        AND Term.Trm_YR >= StObjAwd.EFF_TERM
    )