|
![]() |
Property | Value |
Name | usp_DimStudentObjective_Select |
Schema | dbo |
Is Encrypted | False |
Ansi Nulls Status | True |
Quoted Identifier Status | True |
Description |
Name | Data Type | Direction | Description |
datetime(23, 3) |
Input |
||
datetime(23, 3) |
Input |
Name | Type |
Table |
|
Table |
|
Table |
|
Table |
|
Table |
|
Table |
Name | Type |
TABLE |
|
TABLE |
|
TABLE |
|
TABLE |
|
TABLE |
|
TABLE |
Object | Property | Value |
Object | Property | Value |
/****** Object: StoredProcedure [dbo].[usp_DimStudentObjective_Select] Script Date: 03/09/2017 16:47:10 ******/ SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON /* Name: usp_DimStudentObjective_Select Title: Primary Select for the Student Objective Dimension Date: 05/21/2013 System/Project: Executive Dashboard - Graduation Description: This procedure will combine the Student Objective information from DWStaging for the creation of the Student Objective dimension for the Data Warehouse. Revision History: 01/22/2015 - Sophia Cowan Added IS NOT NULL checking in join for ST_STDNT_A and ST_PROGRAMS_A both in Main Select and WHERE IN UNIONS. Code is marked by -- HETG 02/22/2015 - Sophia Cowan Changed code for GraduationAgeRange to include actual values Include RowIsDuplicate and RowIsSelected column */ /* Primary Student Objective Source SELECT Statement including Incremental Modifications*/ CREATE PROC [dbo].[usp_DimStudentObjective_Select] @IncrementalBeginDtTm datetime , @IncrementalEndDtTm datetime AS SELECT (StdntObj.STDNT_ID + CAST(StdntObj.PGM_ID AS varchar(5)) + CAST(StdntObj.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(StdntObj.SEQ_NUM AS varchar(3)), 3) ) AS StudentObjectiveAK ,StdntObj.STDNT_ID AS StudentId ,LTRIM(RTRIM(ISNULL(StdntObj.PGM_ID, 'N/A'))) 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 ,LTRIM(RTRIM(StdntObj.EFF_TERM)) AS EffectiveTerm ,LTRIM(RTRIM(ISNULL(EffTerm.TRM_TTL, 'N/A'))) AS EffectiveTermTitle ,StdntObj.SEQ_NUM AS SequenceNumber ,CAST(CASE WHEN StdntObj.ADMT_STAT IS NULL THEN 'N/A' WHEN LTRIM(RTRIM(StdntObj.ADMT_STAT)) = '' THEN 'N/A' ELSE StdntObj.ADMT_STAT END AS varchar(3)) AS AdmissionStatus ,LTRIM(RTRIM(CAST(ISNULL(StdntObj.AWD_TYPE, 'N/A') AS varchar(3)))) AS AwardTypeCode ,LTRIM(RTRIM(ISNULL(Award.DESCRIPTION, 'N/A'))) AS AwardType ,CASE WHEN StdntObj.PRIM_FLG = 1 THEN 'Yes' ELSE 'No' END AS PrimaryObjective ,StdntObj.PGM_STAT AS ProgramStatusCode ,ISNULL(CAST(StdntObj.GRAD_STAT AS varchar(3)), 'N/A') AS GraduationStatusCode ,LTRIM(RTRIM(ISNULL(StdntObj.EXPCTD_GRAD_TRM, 'N/A'))) AS ExpectedGraduationTerm ,LTRIM(RTRIM(ISNULL(ExpGradTerm.TRM_TTL, 'N/A'))) AS ExpectedGraduationTermTitle ,LTRIM(RTRIM(ISNULL(StdntObj.ACT_GRAD_TRM, 'N/A'))) AS ActualGraduationTerm ,LTRIM(RTRIM(ISNULL(ActGradTerm.TRM_TTL, 'N/A'))) AS ActualGraduationTermTitle ,CAST(CASE WHEN StdntObj.ACT_GRAD_TRM IS NULL THEN 0 WHEN Stdnt.DOB IS NULL THEN 0 WHEN StdntObj.ACT_GRAD_DT IS NULL THEN 0 WHEN Stdnt.DOB > StdntObj.ACT_GRAD_DT THEN 0 -- When Age > 120, assume incorrect; set to 0. WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) > 120 THEN 0 ELSE CAST(ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) as smallint ) END AS smallint) AS GraduationAge ,CAST( CASE WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) > 0 AND ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) < 19 THEN '< 19' WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) >= 19 AND ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) < 22 THEN '19 - 21' WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) >= 22 AND ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) < 30 THEN '22 - 29' WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) >= 30 AND ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) < 40 THEN '30 - 39' WHEN ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) >= 40 AND ROUND(DATEDIFF(day,CAST(Stdnt.DOB as date),CAST(StdntObj.ACT_GRAD_DT AS date)) / 365.25,0,1) < 65 THEN '40 - 64' ELSE 'Other' END AS varchar(10)) AS GraduationAgeRange ,ISNULL(CAST(StdntObj.GRAD_HNRS AS varchar(3)), 'N/A') AS GraduationHonorsCode ,ISNULL(StdntObj.TRNSFR_MJR, 'N/A') AS TransferMajorCode ,ISNULL(TransferMajor.DESCRIPTION, 'N/A') AS TransferMajor ,ISNULL(StdntObj.TRNSFR_INST, 'N/A') AS TransferInstitutionId ,ISNULL(TransferInstitution.INST_NM, 'N/A') AS TransferInstitution ,LTRIM(RTRIM(ISNULL(StdntObj.TRM_YR_RPTD, 'N/A'))) AS ReportedTerm ,LTRIM(RTRIM(ISNULL(RepTerm.TRM_TTL, 'N/A'))) AS ReportedTermTitle ,CAST('N' AS CHAR(1)) AS RowIsDuplicate ,CAST('Y' AS CHAR(1)) AS RowIsSelected FROM ST_STDNT_OBJ_AWD_A StdntObj /* Get Student Information */ LEFT OUTER JOIN ST_STDNT_A Stdnt ON ( Stdnt.STUDENT_ID = StdntObj.STDNT_ID AND Stdnt.STUDENT_ID IS NOT NULL -- HETG ) /* Get Effective Term Information */ LEFT OUTER JOIN ST_TERM_A EffTerm ON ( EffTerm.TRM_YR = StdntObj.EFF_TERM ) /* Get Expected Graduation Term Information */ LEFT OUTER JOIN ST_TERM_A ExpGradTerm ON ( ExpGradTerm.TRM_YR = StdntObj.EXPCTD_GRAD_TRM ) /* Get Actual Graduation Term Information */ LEFT OUTER JOIN ST_TERM_A ActGradTerm ON ( ActGradTerm.TRM_YR = StdntObj.ACT_GRAD_TRM ) /* Get Reported Term Information */ LEFT OUTER JOIN ST_TERM_A RepTerm ON ( RepTerm.TRM_YR = StdntObj.TRM_YR_RPTD ) /* Get Program Demo Information */ LEFT OUTER JOIN ST_PROGRAMS_A Program ON ( Program.PGM_CD = StdntObj.PGM_ID AND Program.PGM_CD IS NOT NULL -- HETG AND Program.EFF_TRM_D IS NOT NULL -- HETG AND StdntObj.EFF_TERM >= ISNULL(Program.EFF_TRM_D, '999999') AND StdntObj.EFF_TERM <= ISNULL(Program.END_TRM, '999999') ) /* Get Transfer Institution Information */ LEFT OUTER JOIN ST_INSTITUTION_A TransferInstitution ON ( TransferInstitution.INST_TY = 'P' AND TransferInstitution.INST_ID = StdntObj.TRNSFR_INST ) /* Get Transfer Major code description */ LEFT OUTER JOIN UTL_CODE_TABLE TransferMajor ON ( TransferMajor.TABLE_NAME = 'SUS-MAJORS' AND TransferMajor.CODE = StdntObj.TRNSFR_MJR AND TransferMajor.STATUS = 'A' ) /* Get Award Type code description */ LEFT OUTER JOIN UTL_CODE_TABLE Award ON ( Award.TABLE_NAME = 'AWARD-TYPE' AND Award.CODE = StdntObj.AWD_TYPE AND Award.STATUS = 'A' ) /* Determine all the adds/modifies to all related student objective tables which will become the set of records to be processed to the dimension */ WHERE (StdntObj.STDNT_ID + CAST(StdntObj.PGM_ID AS varchar(5)) + CAST(StdntObj.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(StdntObj.SEQ_NUM AS varchar(3)), 3) ) IN ( /* Select changed records from Student Objective file */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_STDNT_OBJ_AWD_A WHERE ( RowUpdatedOn >= @IncrementalBeginDtTm AND RowUpdatedOn <= @IncrementalEndDtTm ) UNION /* Select records from Student Objective file where corresponding Student record has changed (in case DOB changed) */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_STDNT_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.STDNT_ID = ST_STDNT_A.STUDENT_ID ) WHERE ( ST_STDNT_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_STDNT_A.RowUpdatedOn <= @IncrementalEndDtTm AND ST_STDNT_A.STUDENT_ID IS NOT NULL -- HETG ) UNION /* Select records from Student Objective file where Effective Term file record has changed */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_TERM_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.EFF_TERM = ST_TERM_A.TRM_YR ) WHERE ( ST_TERM_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_TERM_A.RowUpdatedOn <= @IncrementalEndDtTm ) UNION /* Select records from Student Objective file where Expected Graduation Term file record has changed */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_TERM_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.EXPCTD_GRAD_TRM = ST_TERM_A.TRM_YR ) WHERE ( ST_TERM_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_TERM_A.RowUpdatedOn <= @IncrementalEndDtTm ) UNION /* Select records from Student Objective file where Actual Graduation Term file record has changed */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_TERM_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.ACT_GRAD_TRM = ST_TERM_A.TRM_YR ) WHERE ( ST_TERM_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_TERM_A.RowUpdatedOn <= @IncrementalEndDtTm ) UNION /* Select records from Student Objective file where Reported Term file record has changed */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_TERM_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.TRM_YR_RPTD = ST_TERM_A.TRM_YR ) WHERE ( ST_TERM_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_TERM_A.RowUpdatedOn <= @IncrementalEndDtTm ) UNION /* Select records from Student Objective file where corresponding Programs Demo record has changed */ SELECT (ST_STDNT_OBJ_AWD_A.STDNT_ID + CAST(ST_STDNT_OBJ_AWD_A.PGM_ID AS varchar(5)) + CAST(ST_STDNT_OBJ_AWD_A.EFF_TERM AS varchar(6)) + RIGHT('000' + CAST(ST_STDNT_OBJ_AWD_A.SEQ_NUM AS varchar(3)), 3)) FROM ST_PROGRAMS_A INNER JOIN ST_STDNT_OBJ_AWD_A ON ( ST_STDNT_OBJ_AWD_A.PGM_ID = ST_PROGRAMS_A.PGM_CD AND ST_STDNT_OBJ_AWD_A.EFF_TERM >= ISNULL(ST_PROGRAMS_A.EFF_TRM_D, '999999') AND ST_STDNT_OBJ_AWD_A.EFF_TERM <= ISNULL(ST_PROGRAMS_A.END_TRM, '999999') ) WHERE ( ST_PROGRAMS_A.RowUpdatedOn >= @IncrementalBeginDtTm AND ST_PROGRAMS_A.RowUpdatedOn <= @IncrementalEndDtTm AND ST_PROGRAMS_A.PGM_CD IS NOT NULL -- HETG AND ST_PROGRAMS_A.EFF_TRM_D IS NOT NULL -- HETG ) ) |
Powered by BI Documenter |