Stored Procedure - usp_DimInstitutionalRetentionCohortT2T_Select

Property	Value
Name	usp_DimInstitutionalRetentionCohortT2T_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_DimInstitutionalRetentionCohortT2T_Select_LogTime] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimInstitutionalRetentionCohortT2T_Select
Title:            Primary Select for the Institutional Retention Cohort Dimension (Term To Term)
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 Term To Term cohorts only.
Revision History:
10/10/2013 Ian Peterson Created.
*/
CREATE PROC [dbo].[usp_DimInstitutionalRetentionCohortT2T_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 and terms from the End Of Term table where the student took a credit class. */
CREATE TABLE #student_term_credit
    (
    [StudentId] VARCHAR(9)  NOT NULL
    ,[T2TCohortTerm] VARCHAR(6) NOT NULL
    )
INSERT INTO #student_term_credit
    (
     [StudentId]
    ,[T2TCohortTerm]
    )
    SELECT
          EOT.STDNT_ID AS StudentId
          ,EOT.TRM_YR AS T2TCohortTerm
    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
        )
    /* We are not interested in students who have a 'summer' cohort term. */
    WHERE
        SUBSTRING(EOT.TRM_YR,5,1) IN ('1','2')
/* 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_term_credit
        ON (
            EndOfTerm.STDNT_ID = #student_term_credit.StudentId
            AND EndOfTerm.TRM_YR = #student_term_credit.T2TCohortTerm
        )
    /* 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,':'))
                ,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
            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 + ' ' + 'FallToSpring'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN EndOfTerm.STDNT_ID + EndOfTerm.TRM_YR + ' ' + 'SpringToFall'
        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 'FallToSpring'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN 'SpringToFall'
        ELSE 'N/A'
        END AS RetentionCohortTypeCode
    ,CASE
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '1' THEN 'Fall To Spring Term Retention'
        WHEN SUBSTRING(EndOfTerm.TRM_YR,5,1) = '2' THEN 'Spring To Fall Term Retention'
        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 T2T cohort term exists on that table */
INNER JOIN #student_term_credit
    ON (
        EndOfTerm.STDNT_ID = #student_term_credit.StudentId
        AND EndOfTerm.TRM_YR = #student_term_credit.T2TCohortTerm
    )
/* Get Cohort Term Information */
LEFT OUTER JOIN (
        SELECT TRM_YR, TRM_TTL FROM ST_TERM_A
    ) Term
    ON (
        Term.TRM_YR = #student_term_credit.T2TCohortTerm
    )
/* 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
    )