Stored Procedure - usp_DimInstitutionalRetentionCohortY2Y

Property	Value
Name	usp_DimInstitutionalRetentionCohortY2Y_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_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] 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.
09/14/2015 Sophia Cowan - Changed LogTime to include 10th of second to retrieve the most current
- Added temp tables to only wok with Students that exist in EndOfTerm file
- Added a temp file to populate from Program Objective Log and reduce number of records
processed
- Remove MAX of Partition and replace with an OUTER APPLY statement
- Include Age Range and RowIsDuplicated and RowIsSelected columns
*/
CREATE PROC [dbo].[usp_DimInstitutionalRetentionCohortY2Y_Select]
AS
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
    ,CAST('a' AS CHAR(1)) AS RowIsDuplicate
    ,CAST('a' AS CHAR(1)) AS RowIsSelected
    END
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
/* This table will contain all Terms and Start Dates for Terms existing in the ST-STDNT-ENDOFTERM table */
CREATE TABLE #Students
(
   StId VARCHAR(9) NOT NULL
)
INSERT INTO #Students
(
   StId
  )

SELECT
  DISTINCT STDNT_ID AS StId
FROM ST_STDNT_ENDOFTERM_A StEndTerm
/* 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
CREATE TABLE #PgmObjLog
(
     [StudentId] VARCHAR(9)  NOT NULL
    ,[PgmId] VARCHAR(5) NOT NULL
    ,[PgmEffTerm] VARCHAR(6) NOT NULL
    ,[AdmitStatus] VARCHAR(1) NOT NULL
    ,LogDateTime DATETIME NOT NULL
)
INSERT INTO #PgmObjLog
(
     [StudentId]
    ,[PgmId]
    ,[PgmEffTerm]
    ,[AdmitStatus]
    ,LogDateTime
)
SELECT
   STDNT_ID
  ,PGM_ID
  ,EFF_TERM
  ,ADMT_STAT
  ,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 PgmObjLog
INNER JOIN #Students
ON
(
   #Students.StId = PgmObjLog.STDNT_ID
)
WHERE STDNT_ID IS NOT NULL
            AND PGM_ID IS NOT NULL
            AND EFF_TERM IS NOT NULL
            AND PGM_STAT != 'IN'
            AND PgmObjLog.ADMT_STAT IS NOT NULL

/* 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)
    ,SessionEndDate DATETIME
    )
INSERT INTO #student_log_temp
    (
     [StudentId]
    ,[CohortProgram]
    ,[CohortTerm]
    ,[PgmEffTerm]
    ,[MaxLogDateTime]
    ,[AdmitStatus]
    ,SessionEndDate
    )
    /* 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
        ,MaxPgmObjLog.PgmEffTerm AS PgmEffTerm
        ,MaxPgmObjLog.MaxLogDateTime
        ,MaxPgmObjLog.AdmitStatus AS AdmitStatus
        ,Sess.SessionEndDate

    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 + ' ' + '23:59:59.000' AS DATETIME) 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. */
   CROSS APPLY
   (
      SELECT TOP 1
         StudentId
        ,PgmId
        ,PgmEffTerm
        ,LogDateTime AS MaxLogDateTime
        ,AdmitStatus
      FROM #PgmObjLog
      WHERE #PgmObjLog.StudentId = EndOfTerm.STDNT_ID
            AND #PgmObjLog.PgmId = EndOfTerm.PGM_ID
             AND #PgmObjLog.PgmEffTerm <= EndOfTerm.TRM_YR
            AND #PgmObjLog.LogDateTime <= Sess.SessionEndDate

       GROUP BY StudentId, PgmId, PgmEffTerm, LogDateTime, AdmitStatus
       ORDER BY StudentId, PgmId, LogDateTime DESC, PgmEffTerm DESC, AdmitStatus
-- ORDER BY StudentId, PgmId, PgmEffTerm DESC, LogDateTime DESC, AdmitStatus
     ) AS MaxPgmObjLog
/* 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(CASE
        WHEN Stdnt.DOB IS NULL THEN 'N/A'
        WHEN Sess.SESS_BEG_DT IS NULL THEN 'N/A'
        WHEN Stdnt.DOB > Sess.SESS_BEG_DT THEN 'N/A'
        -- 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 'N/A'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 0
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 18
            THEN '< 19'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 18
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 21
            THEN '19 - 21'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 21
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 29
            THEN '22 - 29'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 29
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 39
            THEN '30 - 39'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 39
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 64
            THEN '39 - 64'
        WHEN CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) > 65
          AND CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(Sess.SESS_BEG_DT AS date)) / 365.25,0,1) as smallint ) <= 120
            THEN 'Other'
        ELSE 'N/A'
        END AS VARCHAR(10)) AS AgeRangeAtInstitutionalRetentionCohort
    ,CAST('N' AS CHAR(1)) AS RowIsDuplicate
    ,CAST('Y' AS CHAR(1)) AS         RowIsSelected
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')