Stored Procedure - usp_DimProgramRetentionCohort

Property	Value
Name	usp_DimProgramRetentionCohort_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_PROGRAMS_A	Table
ST_SESSION_A	Table
ST_STDNT_A	Table
ST_STDNT_ENDOFTERM_A	Table
ST_STDNT_OBJ_AWD_A	Table
ST_STDNT_OBJ_AWD_LOG_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_PROGRAMS_A	TABLE
ST_SESSION_A	TABLE
ST_STDNT_A	TABLE
ST_STDNT_ENDOFTERM_A	TABLE
ST_STDNT_OBJ_AWD_A	TABLE
ST_STDNT_OBJ_AWD_LOG_A	TABLE
ST_STDNT_TERM_A	TABLE
ST_TERM_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimProgramRetentionCohort_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimProgramRetentionCohort_Select
Title:            Primary Select for the Program Retention Cohort Dimension
Date:            10/10/2013
System/Project:    Student Retention Dashboard
Description:    This procedure will combine the Student End of Term and Academic History information from DWStaging
to determine the Cohort Term for students in each of their objectives.
Revision History:
10/23/2013 Ian Peterson Created.
*/
CREATE PROC [dbo].[usp_DimProgramRetentionCohort_Select]
AS
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
     CAST('aaa' AS varchar(20)) AS ProgramRetentionCohortAK
    ,CAST('aaa' AS varchar(9))  AS StudentId
    ,CAST('aaa' AS varchar(6))  AS CohortTerm
    ,CAST('aaa' AS varchar(30)) AS CohortTermTitle
    ,CAST('aaa' AS varchar(5))  AS ProgramCode
    ,CAST('aaa' AS varchar(30)) AS Program
    ,CAST('aaa' AS varchar(3))  AS ProgramAdmitStatus
    ,CAST('aaa' AS varchar(3))  AS AwardTypeCode
    ,CAST('aaa' AS varchar(30)) AS AwardType
    ,CAST('aaa' AS varchar(10)) AS CIPCode
    ,CAST('aaa' AS varchar(30)) AS CIP
    ,CAST(0 as smallint)        AS AgeAtProgramRetentionCohort
    ,CAST('aaa' AS varchar(10)) AS AgeRangeAtProgramRetentionCohort
    ,CAST('aaa' AS varchar(5))  AS SwitchedProgramCode
    ,CAST('aaa' AS varchar(30)) AS SwitchedProgram
    ,CAST('aaa' AS varchar(6))  AS SwitchedTerm
    ,CAST('aaa' AS varchar(30)) AS SwitchedTermTitle
    ,CAST('aaa' AS varchar(3))  AS SwitchedAwardTypeCode
    ,CAST('aaa' AS varchar(30)) AS SwitchedAwardType
    ,CAST('aaa' AS varchar(10)) AS SwitchedCIPCode
    ,CAST('aaa' AS varchar(30)) AS SwitchedCIP
    END
/* 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_dtemp
    (
    [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_dtemp
    (
     [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 */
/* This table will contain all students and terms containing any switched program info. */
CREATE TABLE #student_switched_temp
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[Term] VARCHAR(6) NOT NULL
    ,[Program] VARCHAR(5) NOT NULL
    ,[GradTerm] VARCHAR(6)
    ,[NewTerm] VARCHAR(6) NOT NULL
    ,[NewProgram] VARCHAR(5) NOT NULL
    )
INSERT INTO #student_switched_temp
    (
     [StudentId]
    ,[Term]
    ,[Program]
    ,[GradTerm]
    ,[NewTerm]
    ,[NewProgram]
    )
    SELECT
         EndOfTerm.STDNT_ID AS StudentId
        ,EndOfTerm.TRM_YR AS Term
        ,EndOfTerm.PGM_ID AS Program
        ,StObjAwd.ACT_GRAD_TRM AS GradTerm
        ,EndOfTerm2.TRM_YR AS NewTerm
        ,EndOfTerm2.PGM_ID AS NewProgram
    FROM ST_STDNT_ENDOFTERM_A EndOfTerm
    /* Join with the first change of program code after the term for that student. */
    /* If no changes of program since that term, then no record will exist in teh above temp table. */
    CROSS APPLY (
        SELECT TOP 1
        STDNT_ID, TRM_YR, PGM_ID, STDNT_GRAD_PROGRESS
        FROM ST_STDNT_ENDOFTERM_A EOT
        WHERE EndOfTerm.STDNT_ID = EOT.STDNT_ID
            AND EndOfTerm.TRM_YR < EOT.TRM_YR
            AND EndOfTerm.PGM_ID <> EOT.PGM_ID
        ORDER BY EOT.TRM_YR
        ) AS EndOfTerm2
    /* 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 (
            EndOfTerm.STDNT_ID = StObjAwd.STDNT_ID
            AND EndOfTerm.PGM_ID = StObjAwd.PGM_ID
            AND EndOfTerm.TRM_YR >= StObjAwd.EFF_TERM
        )
/* This table will contain program objective log records prior to end of cohort term. */
CREATE TABLE #student_log_dtemp
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[CohortProgram] VARCHAR(5) NOT NULL
    ,[CohortTerm] VARCHAR(6) NOT NULL
    ,[PgmEffTerm] VARCHAR(6) NOT NULL
    ,[LogDateTime] DATETIME
    ,[MaxLogDateTime] DATETIME
    ,[AdmitStatus] VARCHAR(1)
    )
INSERT INTO #student_log_dtemp
    (
     [StudentId]
    ,[CohortProgram]
    ,[CohortTerm]
    ,[PgmEffTerm]
    ,[LogDateTime]
    ,[MaxLogDateTime]
    ,[AdmitStatus]
    )
    /* Determine the students and admission status for all log records prior to end of cohort term */
    SELECT
        EndOfTerm.STDNT_ID AS StudentId
        ,EndOfTerm.PGM_ID AS CohortProgram
        ,EndOfTerm.TRM_YR AS CohortTerm
        ,PgmObjLog.EFF_TERM AS PgmEffTerm
        ,PgmObjLog.LogDateTime
        ,MaxLogDateTime = MAX(PgmObjLog.LogDateTime) OVER (PARTITION BY EndOfTerm.STDNT_ID, EndOfTerm.PGM_ID, EndOfTerm.TRM_YR)
        ,PgmObjLog.ADMT_STAT AS AdmitStatus
    FROM ST_STDNT_ENDOFTERM_A EndOfTerm
    /* Inner Join on the End Of Term table so we only bring in students where the actual cohort term exists on that table */
    INNER JOIN #student_program_cohort_dtemp
        ON (
            EndOfTerm.STDNT_ID = #student_program_cohort_dtemp.StudentId
            AND EndOfTerm.TRM_YR = #student_program_cohort_dtemp.CohortTerm
        )
    /* Retrieve the session end date for the cohort term */
    LEFT OUTER JOIN (
            SELECT RTRIM(LTRIM(SUBSTRING(SESSION_KEY,1,6))) AS SessionTerm, CAST(SESS_END_DT AS int) AS SessionEndDate FROM ST_SESSION_A
            WHERE SUBSTRING(SESSION_KEY,7,4) = '1 '  /* Session 1 = Main session */
        ) Sess
        ON (
            Sess.SessionTerm = EndOfTerm.TRM_YR
        )
    /* Get all the log records prior to the session end date for the cohort term and program. */
    LEFT OUTER JOIN (
            SELECT STDNT_ID, PGM_ID, EFF_TERM, SEQ_NUM, ADMT_STAT, LOG_DATE, LOG_TIME
                ,LogDateTime = CONVERT(DATETIME, CONVERT(CHAR(8), LOG_DATE) + ' ' + STUFF(STUFF(RIGHT('000000' + CAST(LOG_TIME/10 AS VARCHAR(6)), 6),3,0,':'),6,0,':'))
            FROM ST_STDNT_OBJ_AWD_LOG_A
            WHERE STDNT_ID IS NOT NULL AND PGM_ID IS NOT NULL AND EFF_TERM IS NOT NULL AND PGM_STAT != 'IN'
        ) PgmObjLog
        ON (
            PgmObjLog.STDNT_ID = EndOfTerm.STDNT_ID
            AND PgmObjLog.PGM_ID = EndOfTerm.PGM_ID
            AND PgmObjLog.EFF_TERM <= EndOfTerm.TRM_YR
            AND PgmObjLog.LOG_DATE <= Sess.SessionEndDate
        )
    WHERE PgmObjLog.ADMT_STAT IS NOT NULL
/* Now the MAIN Select statement. */
/* Get the students that have changed on the End Of Term table. */
SELECT
     EndOfTerm.STDNT_ID + LEFT(EndOfTerm.PGM_ID + ' ',5) + EndOfTerm.TRM_YR AS ProgramRetentionCohortAK
    ,EndOfTerm.STDNT_ID AS StudentId
    ,EndOfTerm.TRM_YR AS CohortTerm
    ,LTRIM(RTRIM(ISNULL(Term.TRM_TTL, 'N/A'))) AS CohortTermTitle
    ,LTRIM(RTRIM(EndOfTerm.PGM_ID)) AS ProgramCode
    ,CAST(CASE
            WHEN Program.PGM_SHRT_TTL IS NULL THEN 'N/A'
            WHEN SUBSTRING(Program.PGM_SHRT_TTL,1,30) <> '' THEN LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,1,30)))
            ELSE LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,31,30)))
        END
        AS varchar(30)) AS Program
    ,CAST(CASE
        WHEN PgmObjLog.AdmitStatus IS NULL THEN 'N/A'
        WHEN LTRIM(RTRIM(PgmObjLog.AdmitStatus)) = '' THEN 'N/A'
        WHEN PgmObjLog.AdmitStatus = 'Y' THEN 'Yes'
        ELSE 'No'
        END AS varchar(3)) AS ProgramAdmitStatus
    ,LTRIM(RTRIM(CAST(ISNULL(Program.AWD_TY, 'N/A') AS varchar(3)))) AS AwardTypeCode
    ,LTRIM(RTRIM(ISNULL(Award.DESCRIPTION, 'N/A'))) AS AwardType
    ,LTRIM(RTRIM(CAST(ISNULL(Program.CIP_CD, 'N/A') AS varchar(10)))) AS CIPCode
    ,LTRIM(RTRIM(ISNULL(CipTable.DESCRIPTION, 'N/A'))) AS CIP
    ,CAST(CASE
        WHEN Stdnt.DOB IS NULL THEN 0
        WHEN Sess.SESS_BEG_DT IS NULL THEN 0
        WHEN Stdnt.DOB > Sess.SESS_BEG_DT THEN 0
        -- When Age > 120, assume incorrect; set to 0.
        WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) > 120 THEN 0
        ELSE CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint )
        END AS smallint) AS AgeAtProgramRetentionCohort
    ,CAST('N/A' as varchar(10)) AS AgeRangeAtProgramRetentionCohort
    ,LTRIM(RTRIM(ISNULL(#student_switched_temp.NewProgram, 'N/A'))) AS SwitchedProgramCode
    ,CAST(CASE
            WHEN SwitchedProgram.PGM_SHRT_TTL IS NULL THEN 'N/A'
            WHEN SUBSTRING(SwitchedProgram.PGM_SHRT_TTL,1,30) <> '' THEN LTRIM(RTRIM(SUBSTRING(SwitchedProgram.PGM_SHRT_TTL,1,30)))
            ELSE LTRIM(RTRIM(SUBSTRING(SwitchedProgram.PGM_SHRT_TTL,31,30)))
        END
        AS varchar(30)) AS SwitchedProgram
    ,LTRIM(RTRIM(ISNULL(#student_switched_temp.NewTerm, 'N/A'))) AS SwitchedTerm
    ,LTRIM(RTRIM(ISNULL(SwitchedTerm.TRM_TTL, 'N/A'))) AS SwitchedTermTitle
    ,LTRIM(RTRIM(CAST(ISNULL(SwitchedProgram.AWD_TY, 'N/A') AS varchar(3)))) AS SwitchedAwardTypeCode
    ,LTRIM(RTRIM(ISNULL(SwitchedAward.DESCRIPTION, 'N/A'))) AS SwitchedAwardType
    ,LTRIM(RTRIM(CAST(ISNULL(SwitchedProgram.CIP_CD, 'N/A') AS varchar(10)))) AS SwitchedCIPCode
    ,LTRIM(RTRIM(ISNULL(SwitchedCipTable.DESCRIPTION, 'N/A'))) AS SwitchedCIP

FROM ST_STDNT_ENDOFTERM_A EndOfTerm
/* Get Student Information */
LEFT OUTER JOIN ST_STDNT_A Stdnt
   ON (
       Stdnt.STUDENT_ID = EndOfTerm.STDNT_ID
      )

/* Inner Join on the End Of Term table so we only bring in students where the actual cohort term exists on that table */
INNER JOIN #student_program_cohort_dtemp
    ON (
        EndOfTerm.STDNT_ID = #student_program_cohort_dtemp.StudentId
        AND EndOfTerm.TRM_YR = #student_program_cohort_dtemp.CohortTerm
    )
/* Get Cohort Term Information */
LEFT OUTER JOIN (
        SELECT TRM_YR, TRM_TTL FROM ST_TERM_A
    ) Term
    ON (
        Term.TRM_YR = #student_program_cohort_dtemp.CohortTerm
    )
/* Get Main Session Information (for term start date to calculate student age) */
LEFT OUTER JOIN ST_SESSION_A Sess
   ON (
        SUBSTRING(Sess.SESSION_KEY,1,6) = EndOfTerm.TRM_YR
            AND SUBSTRING(Sess.SESSION_KEY,7,4) = '1 '
      )
/* Join on the Programs-Demo record to get the award type of the Cohort Term program of study */
/* Gets the latest program-demo record to find award type and program description. */
CROSS APPLY (
    SELECT TOP 1
        PGM_CD, EFF_TRM_D, END_TRM, AWD_TY, CIP_CD, PGM_SHRT_TTL FROM ST_PROGRAMS_A
    WHERE
        PGM_CD IS NOT NULL AND EFF_TRM_D IS NOT NULL AND AWD_TY IS NOT NULL
            ANDST_PROGRAMS_A.PGM_CD = EndOfTerm.PGM_ID
    ORDER BY EFF_TRM_9S_COMP_D
    ) Program
/* Get Award Type code description */
LEFT OUTER JOIN (
        SELECT CODE, UTL_CODE_TABLE.DESCRIPTION FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'AWARD-TYPE' AND UTL_CODE_TABLE.STATUS = 'A'
    ) Award
    ON (
        Award.CODE = Program.AWD_TY
    )
/* Get CIP Code description */
LEFT OUTER JOIN (
        SELECT CODE, UTL_CODE_TABLE.DESCRIPTION FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'CIP-CODES' AND UTL_CODE_TABLE.STATUS = 'A'
    ) CipTable
    ON (
        CipTable.CODE = Program.CIP_CD
    )
/* Outer Join on the Swiched table to see if any program chnages occurred for the student and cohort term. */
/* Student switched program if the grad term is null or graduated after the switch term */
LEFT OUTER JOIN #student_switched_temp
    ON (
        EndOfTerm.STDNT_ID = #student_switched_temp.StudentId
        AND EndOfTerm.TRM_YR = #student_switched_temp.Term
        AND (#student_switched_temp.GradTerm IS NULL OR #student_switched_temp.GradTerm > #student_switched_temp.NewTerm)
    )
/* Get Cohort Term Information */
LEFT OUTER JOIN (
        SELECT TRM_YR, TRM_TTL FROM ST_TERM_A
    ) SwitchedTerm
    ON (
        SwitchedTerm.TRM_YR = #student_switched_temp.NewTerm
    )
/* Join on the Programs-Demo record to get the award type of the Cohort Term program of study */
LEFT OUTER JOIN (
        SELECT PGM_CD, EFF_TRM_D, END_TRM, AWD_TY, CIP_CD, PGM_SHRT_TTL FROM ST_PROGRAMS_A
        WHERE PGM_CD IS NOT NULL AND EFF_TRM_D IS NOT NULL AND AWD_TY IS NOT NULL
    ) SwitchedProgram
    ON (
        SwitchedProgram.PGM_CD = #student_switched_temp.NewProgram
        AND SwitchedProgram.EFF_TRM_D <= #student_switched_temp.NewTerm
        AND (SwitchedProgram.END_TRM IS NULL OR SwitchedProgram.END_TRM >= #student_switched_temp.NewTerm)
    )
/* Get Award Type code description */
LEFT OUTER JOIN (
        SELECT CODE, UTL_CODE_TABLE.DESCRIPTION FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'AWARD-TYPE' AND UTL_CODE_TABLE.STATUS = 'A'
    ) SwitchedAward
    ON (
        SwitchedAward.CODE = SwitchedProgram.AWD_TY
    )
/* Get CIP Code description */
LEFT OUTER JOIN (
        SELECT CODE, UTL_CODE_TABLE.DESCRIPTION FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'CIP-CODES' AND UTL_CODE_TABLE.STATUS = 'A'
    ) SwitchedCipTable
    ON (
        SwitchedCipTable.CODE = SwitchedProgram.CIP_CD
    )
/* Join on the latest Student Objective record (for the program and term) to get the admission status of the student */
LEFT OUTER JOIN #student_log_dtemp PgmObjLog
    ON (
        PgmObjLog.StudentId = EndOfTerm.STDNT_ID
        AND PgmObjLog.CohortProgram = EndOfTerm.PGM_ID
        AND PgmObjLog.CohortTerm = EndOfTerm.TRM_YR
        AND PgmObjLog.LogDateTime = PgmObjLog.MaxLogDateTime
    )