Stored Procedure - usp_DimInstitutionalRetentionCohortY2Y_Select

Property	Value
Name	usp_DimInstitutionalRetentionCohortY2Y_Select_LogTime
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_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_TERM_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimInstitutionalRetentionCohortY2Y_Select_LogTime] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimInstitutionalRetentionCohortY2Y_Select
Title:            Primary Select for the Institutional Retention Cohort Dimension (Year To Year)
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 those students.
This is for the Year To Year cohorts only.
Revision History:
10/10/2013 Ian Peterson Created.
*/
CREATE PROC [dbo].[usp_DimInstitutionalRetentionCohortY2Y_Select_LogTime]
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
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT
     CAST('aaa' AS varchar(30)) AS InstitutionalRetentionCohortAK
    ,CAST('aaa' AS varchar(9))  AS StudentId
    ,CAST(0 AS smallint)        AS CohortYear
    ,CAST('aaa' AS varchar(6))  AS CohortTerm
    ,CAST('aaa' AS varchar(30)) AS CohortTermTitle
    ,CAST('aaa' AS varchar(10)) AS FirstTerm_FT_PT
    ,CAST('aaa' AS varchar(15))    AS RetentionCohortTypeCode
    ,CAST('aaa' AS varchar(60))    AS RetentionCohortType
    ,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(0 as smallint)        AS AgeAtInstitutionalRetentionCohort
    ,CAST('aaa' AS varchar(10)) AS AgeRangeAtInstitutionalRetentionCohort
    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
          ,MIN(AH.CohortTerm) 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, 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_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
                ,CASE
                    WHEN ISDATE(LOG_DATE) = 0 THEN '1900-01-01 00:00:00.000'
                    WHEN LOG_TIME IS NULL THEN CONVERT(DATETIME, CONVERT(CHAR(8), LOG_DATE) + ' '
                            + '00:00:00.000')
                    ELSE CONVERT(DATETIME, CONVERT(CHAR(8), LOG_DATE) + ' '
                        + STUFF(STUFF(STUFF(RIGHT('0000000' + cast(LOG_TIME as varchar(7)),7),3,0,':'),6,0,':'),9,0,'.'))
                END AS                     LogDateTime
                --,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
     CASE
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '1' THEN EndOfTerm.STDNT_ID + EndOfTerm.TRM_YR + ' ' + 'FallToFall'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN EndOfTerm.STDNT_ID + EndOfTerm.TRM_YR + ' ' + 'SpringToSpring'
        ELSE 'N/A'
        END AS InstitutionalRetentionCohortAK
    ,EndOfTerm.STDNT_ID AS StudentId
    ,LEFT(EndOfTerm.TRM_YR,4) AS CohortYear
    ,EndOfTerm.TRM_YR AS CohortTerm
    ,LTRIM(RTRIM(ISNULL(Term.TRM_TTL, 'N/A'))) AS CohortTermTitle
    ,CASE
        WHEN EndOfTerm.TRM_HRS_ATT >= @FTHrs THEN 'Full-time'
        ELSE 'Part-time'
        END AS FirstTerm_FT_PT
    ,CASE
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '1' THEN 'FallToFall'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN 'SpringToSpring'
        ELSE 'N/A'
        END AS RetentionCohortTypeCode
    ,CASE
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '1' THEN 'Fall To Fall Retention By Year'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN 'Spring To Spring Retention By Year'
        ELSE 'N/A'
        END AS RetentionCohortType
    ,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
    ,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 AgeAtInstitutionalRetentionCohort
    ,CAST('N/A' as varchar(10)) AS AgeRangeAtInstitutionalRetentionCohort

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_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 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, 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.CohortTerm = EndOfTerm.TRM_YR
        AND PgmObjLog.LogDateTime = PgmObjLog.MaxLogDateTime
    )
/* We are not interested in students who have a 'summer' cohort term. */
WHERE
    SUBSTRING(EndOfTerm.TRM_YR,5,1) IN ('1','2')