Stored Procedure - usp_DimStudentTerm

Property	Value
Name	usp_DimStudentTerm_Select
Schema	dbo
Is Encrypted	False
Ansi Nulls Status	True
Quoted Identifier Status	True
Description

Name	Data Type	Direction	Description
@IncrementalBeginDtTm	datetime(23, 3)	Input
@IncrementalEndDtTm	datetime(23, 3)	Input

Name	Type
ST_PROGRAMS_A	Table
ST_SESSION_A	Table
ST_STDNT_A	Table
ST_STDNT_ADV_HIST_A	Table
ST_STDNT_TERM_A	Table
ST_TERM_A	Table
UTL_CODE_TABLE	Table

Name	Type
UTL_CODE_TABLE	TABLE
ST_PROGRAMS_A	TABLE
ST_SESSION_A	TABLE
ST_STDNT_A	TABLE
ST_STDNT_ADV_HIST_A	TABLE
ST_STDNT_TERM_A	TABLE
ST_TERM_A	TABLE

Object

Property

Value

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimStudentTerm_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimStudentTerm_Select
Title:            Primary Select for the Student Term Dimension
Date:            11/1/2012
System/Project:    Executive Dashboard - Enrollment
Description:    This procedure will combine the Student Term information from DWStaging for
the creation of the Student Term dimension for the Data Warehouse.
Revision History:
05/02/2013 Harriett Stoddard Modified all varchar attributes to include a left and right trim function.
08/30/2013 Sophia Cowan & Ian Peterson Added in extra join and where conditions to increase efficiency.
*/
/* Primary Student Term Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_DimStudentTerm_Select]
@IncrementalBeginDtTm datetime ,
@IncrementalEndDtTm datetime
AS
SELECT
    (StdntTerm.STDNT_ID + StdntTerm.TRM_YR) AS StudentTermAK
   ,StdntTerm.STDNT_ID AS StudentId
   ,LTRIM(RTRIM(StdntTerm.TRM_YR)) AS Term
   ,LTRIM(RTRIM(ISNULL(Term.TRM_TTL, 'N/A')))  AS TermTitle
   ,LTRIM(RTRIM(ISNULL(StdntTerm.RES_CD, 'N/A'))) AS ResidencyCode
   ,LTRIM(RTRIM(ISNULL(Res.DESCRIPTION, 'N/A'))) AS Residency
   ,LTRIM(RTRIM(ISNULL(StdntTerm.PGM_ID, 'N/A'))) AS ProgramCode
   ,CAST(CASE
            WHEN LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,1,30))) = '' THEN 'N/A'
            WHEN SUBSTRING(Program.PGM_SHRT_TTL,1,30) IS NOT NULL THEN LTRIM(RTRIM(SUBSTRING(Program.PGM_SHRT_TTL,1,30)))
            ELSE 'N/A'
         END
         AS varchar(30)) Program
   ,CAST('Y' AS varchar(3)) AS ProgramAdmitStatus
   ,CAST(CASE
            WHEN StdntTerm.ACDMC_STAT_CD IS NOT NULL THEN LTRIM(RTRIM(StdntTerm.ACDMC_STAT_CD))
            WHEN StdntTerm.ACDMC_STAT_CD_VC IS NOT NULL THEN LTRIM(RTRIM(StdntTerm.ACDMC_STAT_CD_VC))
            WHEN StdntTerm.ACDMC_STAT_CD_HS IS NOT NULL THEN LTRIM(RTRIM(StdntTerm.ACDMC_STAT_CD_HS))
            ELSE 'N/A'
        END
        AS varchar(3)) AS AcademicStatusCode
   ,LTRIM(RTRIM(ISNULL(AcadStat.DESCRIPTION, 'N/A'))) AS AcademicStatus
   ,LTRIM(RTRIM(CAST(ISNULL(SUBSTRING(StdntTerm.ENRLMNT_CD,1,1), 'N/A') AS varchar(3)))) AS BasisOfAdmissionCode
   ,LTRIM(RTRIM(ISNULL(Admiss.DESCRIPTION, 'N/A'))) AS BasisOfAdmission
   ,LTRIM(RTRIM(CAST(ISNULL(SUBSTRING(StdntTerm.ENRLMNT_CD,2,1), 'N/A') AS varchar(3)))) AS EnrollmentStatusCode
   ,LTRIM(RTRIM(ISNULL(Enroll.DESCRIPTION, 'N/A'))) AS EnrollmentStatus
   ,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 AgeAtTerm
   ,CAST('N/A' as varchar(10)) AS AgeRangeAtTerm
   ,LTRIM(RTRIM(CAST(ISNULL(Program.AWD_TY, 'N/A') AS varchar(3)))) AS AwardTypeCode
   ,LTRIM(RTRIM(ISNULL(Award.DESCRIPTION, 'N/A'))) AS AwardType
   ,LTRIM(RTRIM(ISNULL(CAST(Program.SDB_AWARD_TYPE as varchar(3)), 'N/A'))) AS SdbAwardTypeCode
   ,CAST(CASE
            WHEN Advising.STDNT_ID IS NOT NULL THEN 'Yes'
            ELSE 'No'
        END
        AS varchar(3)) AS Advised
   ,LTRIM(RTRIM(ISNULL(CAST(Advising.STATUS_IND as varchar(3)), 'N/A'))) AS AdvisementStatusCode
   ,LTRIM(RTRIM(CAST(ISNULL(AdviseStat.DESCRIPTION, 'N/A') AS varchar(30)))) AS AdvisementStatus
   ,CAST(CASE
            WHEN Advising.OPTIONAL IS NULL THEN 'N/A'
            WHEN Advising.OPTIONAL = '1' THEN 'No'    /* Optional is true/1 => is Not Mandatory */
            ELSE 'Yes'
        END
        AS varchar(3)) AS AdvisementMandatory
   ,CAST(    CASE
            WHEN Advising.MAINT_DATE IS NOT NULL THEN Advising.MAINT_DATE
            WHEN Advising.ADD_DATE IS NOT NULL THEN Advising.ADD_DATE
            ELSE '01/01/1900'  /* No Advising Record */
        END
        AS date) AS DateLastAdvised
   ,LTRIM(RTRIM(ISNULL(Advising.STD_PGM_ID, 'N/A'))) AS AdvisementProgramCode
   ,LTRIM(RTRIM(ISNULL(SUBSTRING(AdviseProgram.PGM_SHRT_TTL,1,30), 'N/A'))) AS AdvisementProgram
   ,LTRIM(RTRIM(CAST(ISNULL(AdviseProgram.AWD_TY, 'N/A') AS varchar(3)))) AS AdvisementAwardTypeCode
   ,LTRIM(RTRIM(ISNULL(AdviseAward.DESCRIPTION, 'N/A'))) AS AdvisementAwardType
   ,LTRIM(RTRIM(CAST(ISNULL(Advising.ADV_ID, 'N/A') AS varchar(8)))) AS AdvisementAdvisorId
   ,LTRIM(RTRIM(CAST(ISNULL(Advisor.ADV_NAME, 'N/A') AS varchar(50)))) AS AdvisementAdvisor
   /* Transfer info commented out as it wasn't reliable on the advising record. */
   /* It should come from the Objective Award file. */
   --,CAST(ISNULL(Advising.TRNSFR_MJR, 'N/A') AS varchar(6)) AS TransferMajorAdvisementCode
   --,ISNULL(Major.DESCRIPTION, 'N/A') AS TransferMajorAdvisement
   --,CAST(ISNULL(Advising.TRNSFR_INST, 'N/A') AS varchar(8)) AS TransferInstitutionAdvisementId
   --,CAST(ISNULL(TransferInstitution.INST_NM, 'N/A') AS varchar(50)) AS TransferInstitutionAdvisement
   ,'N/A' AS TransferMajorAdvisementCode
   ,'N/A' AS TransferMajorAdvisement
   ,'N/A' AS TransferInstitutionAdvisementId
   ,'N/A' AS TransferInstitutionAdvisement
FROM ST_STDNT_TERM_A StdntTerm
/* Get Student Information */
LEFT OUTER JOIN ST_STDNT_A Stdnt
   ON (
       Stdnt.STUDENT_ID = StdntTerm.STDNT_ID
      )

/* Get Term Information */
LEFT OUTER JOIN ST_TERM_A Term
   ON (
       Term.TRM_YR = StdntTerm.TRM_YR
      )

/* 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) = StdntTerm.TRM_YR
            AND SUBSTRING(Sess.SESSION_KEY,7,4) = '1 '
      )
/* Get Student Term Advising Information */
LEFT OUTER JOIN ST_STDNT_ADV_HIST_A Advising
   ON (
       Advising.STDNT_ID = StdntTerm.STDNT_ID
            AND Advising.APPL_TRM = StdntTerm.TRM_YR
      )

/* Get Advisor Information */
LEFT OUTER JOIN ST_STDNT_ADV_HIST_A Advisor
   ON (
       Advisor.ADV_ID = Advising.ADV_ID
            AND Advisor.STDNT_ID IS                                     NULL/* 08/30/2013: Do not get student advising records */
            AND Advising.APPL_TRM >= ISNULL(Advisor.ADV_EFF_TRM, '999999')
            AND Advising.APPL_TRM <= ISNULL(Advisor.ADV_END_TRM, '999999')
      )

/* Get Program Demo Information */
LEFT OUTER JOIN ST_PROGRAMS_A Program
   ON (
       Program.PGM_CD = StdntTerm.PGM_ID
            AND Program.PGM_CD IS NOT NULL AND Program.EFF_TRM_D IS NOT NULL /* 08/30/2013: Only Get Program Demo Info */
            AND StdntTerm.TRM_YR >= ISNULL(Program.EFF_TRM_D, '999999')
            AND StdntTerm.TRM_YR <= ISNULL(Program.END_TRM, '999999')
       )
/* Get Advisement Program Demo Information */
LEFT OUTER JOIN ST_PROGRAMS_A AdviseProgram
   ON (
        AdviseProgram.PGM_CD = Advising.STD_PGM_ID
            AND AdviseProgram.PGM_CD IS NOT NULL and AdviseProgram.EFF_TRM_D IS NOT NULL  /* 08/30/2013: Only Get Program Demo Info */
            AND Advising.APPL_TRM >= ISNULL(AdviseProgram.EFF_TRM_D, '999999')
            AND Advising.APPL_TRM <= ISNULL(AdviseProgram.END_TRM, '999999')
       )
/* Commented out as the transfer institution should not come from the Advising record. */
/* Get Transfer Institution Information */
--LEFT OUTER JOIN ST_INSTITUTION_A TransferInstitution
-- ON (
-- TransferInstitution.INST_TY = 'P'
--            AND TransferInstitution.INST_ID = Advising.TRNSFR_INST
-- )

/* Get Residency code description */
LEFT OUTER JOIN UTL_CODE_TABLE Res
   ON (
       Res.TABLE_NAME = 'RESIDENCY'
            AND Res.CODE = StdntTerm.RES_CD
            AND Res.STATUS = 'A'
       )

/* Get Academic Status code description */
/* Check the credit status first (if entered), then vocational, then high-school */
LEFT OUTER JOIN UTL_CODE_TABLE AcadStat
   ON (
        StdntTerm.ACDMC_STAT_CD IS NOT NULL
            AND AcadStat.TABLE_NAME = 'ACAD-STAT'
            AND AcadStat.CODE = StdntTerm.ACDMC_STAT_CD
            AND AcadStat.STATUS = 'A'
        OR
        StdntTerm.ACDMC_STAT_CD IS NULL
            AND StdntTerm.ACDMC_STAT_CD_VC IS NOT NULL
            AND AcadStat.TABLE_NAME = 'ACAD-STAT'
            AND AcadStat.CODE = StdntTerm.ACDMC_STAT_CD_VC
            AND AcadStat.STATUS = 'A'
        OR
        StdntTerm.ACDMC_STAT_CD IS NULL
            AND StdntTerm.ACDMC_STAT_CD_VC IS NULL
            AND StdntTerm.ACDMC_STAT_CD_HS IS NOT NULL
            AND AcadStat.TABLE_NAME = 'ACAD-STAT'
            AND AcadStat.CODE = StdntTerm.ACDMC_STAT_CD_HS
            AND AcadStat.STATUS = 'A'
       )
/* Get Basis of Admission code description */
LEFT OUTER JOIN UTL_CODE_TABLE Admiss
   ON (
       Admiss.TABLE_NAME = 'BASIS-ADM'
            AND Admiss.CODE = SUBSTRING(StdntTerm.ENRLMNT_CD,1,1)
            AND Admiss.STATUS = 'A'
       )
/* Get Enrollment code description */
LEFT OUTER JOIN UTL_CODE_TABLE Enroll
   ON (
       Enroll.TABLE_NAME = 'ENRL-STAT'
            AND Enroll.CODE = SUBSTRING(StdntTerm.ENRLMNT_CD,2,1)
            AND Enroll.STATUS = 'A'
       )
/* Get Award Type code description */
LEFT OUTER JOIN UTL_CODE_TABLE Award
   ON (
       Award.TABLE_NAME = 'AWARD-TYPE'
            AND Award.CODE = Program.AWD_TY
            AND Award.STATUS = 'A'
       )
/* Get Advisement Status code description */
LEFT OUTER JOIN UTL_CODE_TABLE AdviseStat
   ON (
       AdviseStat.TABLE_NAME = 'ADV-STATUS'
            AND AdviseStat.CODE = Advising.STATUS_IND
            AND AdviseStat.STATUS = 'A'
       )
/* Get Advisement Award Type code description */
LEFT OUTER JOIN UTL_CODE_TABLE AdviseAward
   ON (
       AdviseAward.TABLE_NAME = 'AWARD-TYPE'
            AND AdviseAward.CODE = AdviseProgram.AWD_TY
            AND AdviseAward.STATUS = 'A'
       )
/* Commented out as the transfer major should not come from the Advising record. */
/* Get Advisement Transfer Major code description */
--LEFT OUTER JOIN UTL_CODE_TABLE Major
-- ON (
-- Major.TABLE_NAME = 'SUS-MAJORS'
--            AND Major.CODE = Advising.TRNSFR_MJR
--            AND Major.STATUS = 'A'
-- )
/* Determine all the adds/modifies to all related student term tables which will
become the set of records to be processed to the dimension */
WHERE (StdntTerm.STDNT_ID + StdntTerm.TRM_YR) IN
(
/* Select changed records from Student Term file */
SELECT (STDNT_ID + TRM_YR)
FROM ST_STDNT_TERM_A
WHERE (
       RowUpdatedOn >= @IncrementalBeginDtTm AND
       RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select records from Student Term file where Student file record has changed */
SELECT     DISTINCT(ST_STDNT_TERM_A.STDNT_ID + ST_STDNT_TERM_A.TRM_YR)
FROM ST_STDNT_A
INNER JOIN ST_STDNT_TERM_A
    ON (
        ST_STDNT_TERM_A.STDNT_ID = ST_STDNT_A.STUDENT_ID
        )
WHERE (
       ST_STDNT_A.RowUpdatedOn >= @IncrementalBeginDtTm AND
       ST_STDNT_A.RowUpdatedOn <= @IncrementalEndDtTm
     )
UNION
/* Select records from Student Term file where Term file record has changed */
SELECT     DISTINCT(ST_STDNT_TERM_A.STDNT_ID + ST_STDNT_TERM_A.TRM_YR)
FROM ST_TERM_A
INNER JOIN ST_STDNT_TERM_A
    ON (
        ST_STDNT_TERM_A.TRM_YR = ST_TERM_A.TRM_YR
        )
WHERE (
       ST_TERM_A.RowUpdatedOn >= @IncrementalBeginDtTm AND
       ST_TERM_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select changed records from Student Term Advising file */
SELECT (STDNT_ID + APPL_TRM)
FROM ST_STDNT_ADV_HIST_A
WHERE (
       RowUpdatedOn >= @IncrementalBeginDtTm
       AND RowUpdatedOn <= @IncrementalEndDtTm
       AND STDNT_ID IS NOT NULL AND APPL_TRM IS NOT NULL  /* 08/30/2013: Do Not Get Advisor Info */
      )
UNION
/* Select records from Student Term Advising file where corresponding Advisor file record has changed */
SELECT     DISTINCT(Advising.STDNT_ID + Advising.APPL_TRM)
FROM ST_STDNT_ADV_HIST_A Advisor
INNER JOIN ST_STDNT_ADV_HIST_A Advising
   ON (
       Advisor.ADV_ID = Advising.ADV_ID
            AND Advising.APPL_TRM >= ISNULL(Advisor.ADV_EFF_TRM, '999999')
            AND Advising.APPL_TRM <= ISNULL(Advisor.ADV_END_TRM, '999999')
      )
WHERE (
       Advisor.RowUpdatedOn >= @IncrementalBeginDtTm
       AND Advisor.RowUpdatedOn <= @IncrementalEndDtTm
       AND Advisor.STDNT_ID IS                         NULL/* 08/30/2013: Get Advisor Info - Not Student Advising Info */
      )
UNION
/* Select records from Student Term file where corresponding Programs Demo record has changed */
SELECT     DISTINCT(ST_STDNT_TERM_A.STDNT_ID + ST_STDNT_TERM_A.TRM_YR)
FROM ST_PROGRAMS_A
INNER JOIN ST_STDNT_TERM_A
    ON (
        ST_STDNT_TERM_A.PGM_ID = ST_PROGRAMS_A.PGM_CD
            AND ST_STDNT_TERM_A.TRM_YR >= ISNULL(ST_PROGRAMS_A.EFF_TRM_D, '999999')
            AND ST_STDNT_TERM_A.TRM_YR <= 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 AND ST_PROGRAMS_A.EFF_TRM_D IS NOT NULL /* 08/30/2013: Only Get Program Demo Info */
      )
UNION
/* Select records from Student Term Advising file where corresponding Advised Program record has changed */
SELECT     DISTINCT(ST_STDNT_ADV_HIST_A.STDNT_ID + ST_STDNT_ADV_HIST_A.APPL_TRM)
FROM ST_PROGRAMS_A
INNER JOIN ST_STDNT_ADV_HIST_A
   ON (
        ST_PROGRAMS_A.PGM_CD = ST_STDNT_ADV_HIST_A.STD_PGM_ID
            AND ST_STDNT_ADV_HIST_A.APPL_TRM >= ISNULL(ST_PROGRAMS_A.EFF_TRM_D, '999999')
            AND ST_STDNT_ADV_HIST_A.APPL_TRM <= 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 AND ST_PROGRAMS_A.EFF_TRM_D IS NOT NULL /* 08/30/2013: Only Get Program Demo Info */
      )
--UNION
/* Commented out as the transfer institution should not come from the Advising record. */
/* Select records from Student Term Advising file where corresponding Transfer Institution record has changed */
--SELECT DISTINCT    (ST_STDNT_ADV_HIST_A.STDNT_ID + ST_STDNT_ADV_HIST_A.APPL_TRM)
--FROM ST_INSTITUTION_A
--INNER JOIN ST_STDNT_ADV_HIST_A
-- ON (
--        ST_INSTITUTION_A.INST_ID = ST_STDNT_ADV_HIST_A.TRNSFR_INST
-- )
--WHERE (
--     ST_INSTITUTION_A.INST_TY = 'P' AND
-- ST_INSTITUTION_A.RowUpdatedOn >= @IncrementalBeginDtTm AND
-- ST_INSTITUTION_A.RowUpdatedOn <= @IncrementalEndDtTm
-- )
)