DWStaging
 usp_DimStudentObjective_Select (Stored Procedure)
  Properties
Property Value
Name usp_DimStudentObjective_Select
Schema dbo
Is Encrypted False
Ansi Nulls Status True
Quoted Identifier Status True
Description
  Parameters
Name Data Type Direction Description
datetime(23, 3)
Input
datetime(23, 3)
Input
  Parent Dependencies (objects that usp_DimStudentObjective_Select depends on)
Name Type
Table
Table
Table
Table
Table
Table
  Child Dependencies (objects that depend on usp_DimStudentObjective_Select)
Name Type
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
  Extended Properties
Object Property Value
   Annotations
Object Property Value
  DDL
/****** 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