|
![]() |
Property | Value |
Name | usp_FactInstitutionalRetentionY2YSnapshot_Select |
Schema | dbo |
Is Encrypted | False |
Ansi Nulls Status | True |
Quoted Identifier Status | True |
Description |
Name | Data Type | Direction | Description |
Name | Type |
Table |
|
Table |
|
Table |
Name | Type |
TABLE |
|
TABLE |
|
TABLE |
Object | Property | Value |
Object | Property | Value |
/****** 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 |