Stored Procedure - usp_DimStudentSuccessCohort

Property	Value
Name	usp_DimStudentSuccessCohort_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_LOG_A	Table
ST_STDNT_TEST_DEMO_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_LOG_A	TABLE
ST_STDNT_TEST_DEMO_A	TABLE
ST_TERM_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimStudentSuccessCohort_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimStudentSuccessCohort_Select
Title:            Primary Select for the Student Success Cohort Dimension
Date:            09/03/2013
System/Project:    Student Success Dashboard
Description:    This procedure will combine the Student End of Term and Academic History information from DWStaging
to determine the Cohort Term for those students.
Revision History:
09/03/2013 Ian Peterson Created.
11/21/2013 Ian Peterson Modified to pick up program info for programs that had ended by the cohort term of a student.
*/
CREATE PROC [dbo].[usp_DimStudentSuccessCohort_Select]
AS
/* Determine the number of hours required to be considered Full-Time and put into a local variable. */
DECLARE @FTHrs numeric
SET @FTHrs = (SELECT CAST(SUBSTRING(DESCRIPTION,1,4) AS NUMERIC) FROM UTL_CODE_TABLE
        WHERE TABLE_NAME = 'SITE-PARAM'
            AND CODE = 'ENROLLMENT'
            AND [STATUS] = 'A')
IF @FTHrs IS NULL
BEGIN
    SET @FTHrs =         12/* Default to 12 hours if table does not exist. */
END
--PRINT @FTHrs /* Print the local variable value to the screen */
/* 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(15)) AS StudentSuccessCohortAK
    ,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(6))  AS FirstYearTerm
    ,CAST('aaa' AS varchar(6))  AS LastTermCaptured
    ,CAST('aaa' AS varchar(30)) AS LastTermCapturedTitle
    ,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(20)) AS InitialPrepStatus
    ,CAST('aaa' AS varchar(10)) AS FirstTerm_FT_PT
    END
/* This table will contain all students from the End Of Term table and their real cohort terms. */
CREATE TABLE #student_cohort_temp
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[CohortTerm] VARCHAR(6) NOT NULL
    )
INSERT INTO #student_cohort_temp
    (
     [StudentId]
    ,[CohortTerm]
    )
    /* Determine the students and their cohort term from the Student End Of Term table */
    SELECT
          EOT.STDNT_ID AS StudentId
          ,CASE
            /* If summer term, then change to following fall term. */
            WHEN SUBSTRING(MIN(AH.CohortTerm),5,1) = '3' OR SUBSTRING(MIN(AH.CohortTerm),5,1) = '4'
                THEN CAST((CAST(SUBSTRING(MIN(AH.CohortTerm),1,4) AS int) + 1) AS varchar(4)) + '1'
            ELSE MIN(AH.CohortTerm)
           END AS CohortTerm
    FROM ST_STDNT_ENDOFTERM_A EOT
    /* Join on set of students that completed a local credit class after the HS Grad Date */
    /* The earliest such term is their cohort term. */
    INNER JOIN (
            /* Get first term that the student took a credit class after HS Graduation. This is Cohort Term. */
            SELECT
                AH2.STUDENT_ID
                ,MIN(AH2.AH_TERM) AS CohortTerm
            FROM (
                /* Join AH with Student to get AH local credit classes taken after HS Grad Date */
                SELECT
                    AH1.STUDENT_ID,
                    RTRIM(SUBSTRING(CLASS_KEY,12,6)) AS AH_TERM
                FROM ST_ACDMC_HIST_A AH1
                /* Join on Student file to get the High School Graduation Date */
                LEFT OUTER JOIN (
                    SELECT STUDENT_ID, HS_GRAD_DT FROM ST_STDNT_A
                        WHERE STUDENT_ID IS NOT NULL
                    ) ST
                    ON (
                        ST.STUDENT_ID = AH1.STUDENT_ID
                    )
                WHERE SUBSTRING(CLASS_KEY,1,1) = 'C'  /* Credit Class */
                AND AH1.SPCL_CRED_TY IS     NULL/* No test classes */
                AND (ST.HS_GRAD_DT IS NULL OR ST.HS_GRAD_DT <= SUBSTRING(SESS_END_BEG_DT,9,8))
            ) AH2
            GROUP BY AH2.STUDENT_ID
        ) AH
        ON (
            EOT.STDNT_ID = AH.STUDENT_ID
        )
    GROUP BY EOT.STDNT_ID
    ORDER BY EOT.STDNT_ID
/* This table will contain program objective log records prior to end of cohort term. */
CREATE TABLE #student_log_temp
    (
    [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_temp
    (
     [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)
        ,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_cohort_temp
        ON (
            EndOfTerm.STDNT_ID = #student_cohort_temp.StudentId
            AND EndOfTerm.TRM_YR = #student_cohort_temp.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 + EndOfTerm.TRM_YR) AS StudentSuccessCohortAK
    ,EndOfTerm.STDNT_ID AS StudentId
    ,EndOfTerm.TRM_YR AS CohortTerm
    ,LTRIM(RTRIM(ISNULL(Term.TRM_TTL, 'N/A'))) AS CohortTermTitle
    ,CASE
        WHEN @SummerTerms = 2 AND RIGHT(EndOfTerm.TRM_YR,1) = '1' THEN SUBSTRING(EndOfTerm.TRM_YR,1,4) + '4'
        WHEN @SummerTerms = 1 AND RIGHT(EndOfTerm.TRM_YR,1) = '1' THEN SUBSTRING(EndOfTerm.TRM_YR,1,4) + '3'
        ELSE CAST((CAST(SUBSTRING(EndOfTerm.TRM_YR,1,4) AS INT) + 1) AS VARCHAR(4)) + '1'
     END AS FirstYearTerm
    ,MaxTerm.LastTermCaptured AS LastTermCaptured
    ,LTRIM(RTRIM(ISNULL(LastTerm.TRM_TTL, 'N/A'))) AS LastTermCapturedTitle
    ,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
    ,CASE
        WHEN (EngPlcmnt.INIT_STATUS_IND = 'R' OR EngPlcmnt.INIT_STATUS_IND = 'U') AND
            (MatPlcmnt.INIT_STATUS_IND = 'R' OR MatPlcmnt.INIT_STATUS_IND = 'U') AND
            (ReaPlcmnt.INIT_STATUS_IND = 'R' OR ReaPlcmnt.INIT_STATUS_IND = 'U')
            THEN 'Unspecified'
        WHEN (EngPlcmnt.INIT_STATUS_IND = 'I' OR EngPlcmnt.INIT_STATUS_IND = 'E' OR EngPlcmnt.INIT_STATUS_IND = 'C') OR
           (MatPlcmnt.INIT_STATUS_IND = 'I' OR MatPlcmnt.INIT_STATUS_IND = 'E' OR MatPlcmnt.INIT_STATUS_IND = 'C') OR
           (ReaPlcmnt.INIT_STATUS_IND = 'I' OR ReaPlcmnt.INIT_STATUS_IND = 'E' OR ReaPlcmnt.INIT_STATUS_IND = 'C')
            THEN 'Required Prep'
        ELSE 'Did Not Require Prep'
    END AS InitialPrepStatus
    ,CASE
        WHEN EndOfTerm.TRM_HRS_ATT >= @FTHrs THEN 'Full-time'
        ELSE 'Part-time'
    END AS FirstTerm_FT_PT

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_cohort_temp
    ON (
        EndOfTerm.STDNT_ID = #student_cohort_temp.StudentId
        AND EndOfTerm.TRM_YR = #student_cohort_temp.CohortTerm
    )
/* Get Cohort Term Information */
LEFT OUTER JOIN (
        SELECT TRM_YR, TRM_TTL FROM ST_TERM_A
    ) Term
    ON (
        Term.TRM_YR = #student_cohort_temp.CohortTerm
    )
/* Get Last Term Captured for each student. */
LEFT OUTER JOIN (
        SELECT
            STDNT_ID AS StudentId
            ,MAX(TRM_YR) AS LastTermCaptured
        FROM ST_STDNT_ENDOFTERM_A
        GROUP BY STDNT_ID
    ) MaxTerm
    ON (
        EndOfTerm.STDNT_ID = MaxTerm.StudentId
    )
/* Get Last Term Captured Information */
LEFT OUTER JOIN (
        SELECT TRM_YR, TRM_TTL FROM ST_TERM_A
    ) LastTerm
    ON (
        LastTerm.TRM_YR = MaxTerm.LastTermCaptured
    )
/* 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, 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
    )
/* 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_temp PgmObjLog
    ON (
        PgmObjLog.StudentId = EndOfTerm.STDNT_ID
        AND PgmObjLog.CohortProgram = EndOfTerm.PGM_ID
        AND PgmObjLog.PgmEffTerm <= EndOfTerm.TRM_YR
        AND PgmObjLog.LogDateTime = PgmObjLog.MaxLogDateTime
    )
/* Get English Placement */
LEFT OUTER JOIN (
        SELECT STDNT_ID, INIT_STATUS_IND FROM ST_STDNT_TEST_DEMO_A
        WHERE PLACEMENT_AREA = 'ENG'
    ) EngPlcmnt
    ON (
        EngPlcmnt.STDNT_ID = EndOfTerm.STDNT_ID
    )
/* Get Math Placement */
LEFT OUTER JOIN (
        SELECT STDNT_ID, INIT_STATUS_IND FROM ST_STDNT_TEST_DEMO_A
        WHERE PLACEMENT_AREA = 'MAT'
    ) MatPlcmnt
    ON (
        MatPlcmnt.STDNT_ID = EndOfTerm.STDNT_ID
    )
/* Get Reading Placement */
LEFT OUTER JOIN (
        SELECT STDNT_ID, INIT_STATUS_IND FROM ST_STDNT_TEST_DEMO_A
        WHERE PLACEMENT_AREA = 'REA'
    ) ReaPlcmnt
    ON (
        ReaPlcmnt.STDNT_ID = EndOfTerm.STDNT_ID
    )
/* Only interested in students having the following award types for their Cohort Term: (AA, AAS, AS, B*). */
/*    SUBSTRING(EndOfTerm.PGM_ID,1,1) IN ('1','2','A','C','E','G','J','R','S','T') */
WHERE
    (Program.AWD_TY IN ('AA','AAS','AS') OR SUBSTRING(Program.AWD_TY,1,1) = 'B')