DWStaging
 usp_FactInstitutionalRetentionY2YSnapshot_Select (Stored Procedure)
  Properties
Property Value
Name usp_FactInstitutionalRetentionY2YSnapshot_Select
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
  Parent Dependencies (objects that usp_FactInstitutionalRetentionY2YSnapshot_Select depends on)
Name Type
Table
Table
Table
  Child Dependencies (objects that depend on usp_FactInstitutionalRetentionY2YSnapshot_Select)
Name Type
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** Object: StoredProcedure [dbo].[usp_FactInstitutionalRetentionY2YSnapshot_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_FactInstitutionalRetentionY2YSnapshot_Select
Title:            Primary Select for the Institutional Retention Cohort Fact (Year To Year)
Date:            10/10/2013
System/Project:    Student Retention Dashboard
Description:    This procedure will build the Fact records for the Year to Year Institutional Retention records.
Revision History:
10/10/2013 Ian Peterson Created.
*/
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_FactInstitutionalRetentionY2YSnapshot_Select]
AS 
SET NOCOUNT ON
IF 2=3
    BEGIN
    SELECT 
          CAST('aaa' AS varchar(9)) AS StudentId
        , CAST('aaa' AS varchar(6)) AS InstitutionalRetentionCohortTerm
        , CAST('aaa' AS varchar(6)) AS InstitutionalRetentionTrackingTerm
        , CAST('aaa' AS varchar(15)) AS RetentionCohortTypeCode
        , CAST(1 as smallint) as RetentionYearCount
        , CAST(1 as smallint) as RetentionTermCount
        , CAST(1 as smallint) as EnrolledInTerm
        , CAST(1 as smallint) as Graduated
        , CAST(1 as smallint) as NotEnrolledInTerm
    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
SELECT
     EndOfTerm.STDNT_ID AS StudentId
    ,GetCohort.CohortTerm AS InstitutionalRetentionCohortTerm
    ,EndOfTerm.TRM_YR AS InstitutionalRetentionTrackingTerm
    ,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
    ,(CAST(LEFT(EndOfTerm.TRM_YR,4) AS INT) - CAST(LEFT(GetCohort.CohortTerm,4) AS INT) + 1) AS RetentionYearCount
    ,0 AS RetentionTermCount
    ,CASE
        WHEN EndOfTerm.STDNT_GRAD_PROGRESS = 'GR' THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'Y' THEN 1
        ELSE 0
        END AS EnrolledInTerm
    ,CASE
        WHEN EndOfTerm.STDNT_GRAD_PROGRESS = 'GR' THEN 1
        ELSE 0
        END AS Graduated
    ,CASE
        WHEN EndOfTerm.STDNT_GRAD_PROGRESS = 'GR' THEN 0
        WHEN EndOfTerm.STDNT_ENROLLED_FLG = 'N' THEN 1
        ELSE 0
        END AS NotEnrolledInTerm
FROM ST_STDNT_ENDOFTERM_A AS EndOfTerm
/* Inner Join to determine the student's cohort term. */
INNER JOIN #student_cohort_temp AS GetCohort
    ON (
        EndOfTerm.STDNT_ID = GetCohort.StudentId
    )
/* Inner Join to make sure the student's cohort term exists on the End of Term table. */
INNER JOIN ST_STDNT_ENDOFTERM_A AS CohortExists
    ON (
        GetCohort.StudentId = CohortExists.STDNT_ID
        AND GetCohort.CohortTerm = CohortExists.TRM_YR
    )
WHERE
    /* Only interested in Fall and Spring cohorts. */
    SUBSTRING(EndOfTerm.TRM_YR,5,1) IN ('1','2')
    /* Tracking term must be same term type as cohort term. */
    AND SUBSTRING(EndOfTerm.TRM_YR,5,1) = SUBSTRING(GetCohort.CohortTerm,5,1)
    /* Tracking term must not be before cohort term. */
    AND EndOfTerm.TRM_YR >= GetCohort.CohortTerm
    /* Only track year-to-year for cohort and following 6 years. */
    AND CAST(LEFT(EndOfTerm.TRM_YR,4) AS INT) - CAST(LEFT(GetCohort.CohortTerm,4) AS INT) <= 6
Powered by BI Documenter