Stored Procedure - usp_DimStudent

Property	Value
Name	usp_DimStudent_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
@CurrentDayDate	datetime(23, 3)	Input

Name	Type
FAC192_SITE_A	Table
ST_ADDRESSES_A	Table
ST_EXTRNL_CRDNTL_A	Table
ST_INSTITUTION_A	Table
ST_RESTRICTED_INFO_A	Table
ST_STDNT_A	Table
ST_STDNT_SPCL_DSGNTR_A	Table
ST_STDNT_TEST_DEMO_A	Table
ST_VA_INT_A	Table
UTL_CODE_TABLE	Table
UTL_CODE_TABLE_GENERIC	Table
uvw_CODE_Country	View
uvw_CODE_County	View
uvw_CODE_Language	View
uvw_CODE_State	View
uvw_StudentRace	View

Name	Type
uvw_CODE_Country	VIEW
uvw_CODE_County	VIEW
uvw_CODE_Language	VIEW
uvw_CODE_State	VIEW
uvw_StudentRace	VIEW
UTL_CODE_TABLE	TABLE
UTL_CODE_TABLE_GENERIC	TABLE
FAC192_SITE_A	TABLE
ST_ADDRESSES_A	TABLE
ST_EXTRNL_CRDNTL_A	TABLE
ST_INSTITUTION_A	TABLE
ST_RESTRICTED_INFO_A	TABLE
ST_STDNT_A	TABLE
ST_STDNT_SPCL_DSGNTR_A	TABLE
ST_STDNT_TEST_DEMO_A	TABLE
ST_VA_INT_A	TABLE
udf_StringToDate	USERDEFINEDFUNCTION

Object	Property	Value
usp_DimStudent_Select	Description	The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database. This Stored Procedure pulls information from the following tables: ST_STDNT
@IncrementalBeginDtTm	Description	The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database. This Stored Procedure pulls information from the following tables: ST_STDNT
@IncrementalEndDtTm	Description	The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database. This Stored Procedure pulls information from the following tables: ST_STDNT
@CurrentDayDate	Description	The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database. This Stored Procedure pulls information from the following tables: ST_STDNT

Object

Property

Value

/****** Object: StoredProcedure [dbo].[usp_DimStudent_Select] Script Date: 03/09/2017 16:47:10 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
/*
Name:            usp_DimStudent_Select
Title:            Primary Select for the Student Dimension
Date:            11/1/2012
System/Project:    Executive Dashboard - Enrollment
Description:    This procedure will combine the Student information from DWStaging for
the creation of the Student dimension for the Data Warehouse.
Revision History:
05/02/2013 Harriett Stoddard    Modified all varchar attributes to include a left and right trim function.
05/08/2013 Ian Peterson        Added columns for HomeCampusCode, HomeCampus, InitialPrepStatus,
                                VeteranStudent, StudentAthlete and InternationalStudent.
                                This also included outer join statements to get the data.
05/01/2014 Sophia Cowan Added ISDATE Checking to DOB
*/
/* Primary Student Source SELECT Statement including Incremental Modifications*/
CREATE PROC [dbo].[usp_DimStudent_Select]
@IncrementalBeginDtTm datetime, /* Date and Time to validate against RowUpdatedOn in Source Tables */
@IncrementalEndDtTm datetime,
@CurrentDayDate datetime
AS
DECLARE @CurrentYYYYMMDD varchar(8)
DECLARE @TempDate varchar(10)
DECLARE @Notime date
SET @Notime = CAST(@CurrentDayDate AS DATE)
/* Convert Date yyyy-mm-dd to yyyymmdd */
SET @TempDate = CAST(@Notime AS varchar(10))
SET @CurrentYYYYMMDD = LEFT(@TempDate,4) +
    SUBSTRING(@TempDate,6,2) +
    RIGHT(@TempDate,2)
SELECT
    Stdnt.STUDENT_ID AS StudentIdAK
  , Stdnt.STUDENT_ID AS StudentId
  , LTRIM(RTRIM(Stdnt.LST_NM)) AS LastName
  , LTRIM(RTRIM(Stdnt.FRST_NM)) AS FirstName
  , LTRIM(RTRIM(Stdnt.MDL_NM)) AS MiddleName
  , CAST(ISNULL(Stdnt.LST_NM, 'N/A') + ', '
        + ISNULL(Stdnt.FRST_NM, ' ') + ' '
        + ISNULL(Stdnt.MDL_NM, ' ')
        AS varchar(50)) AS Fullname
  , CASE
        WHEN Stdnt.SEX = 'M' THEN 'Male'
        WHEN Stdnt.SEX = 'F' THEN 'Female'
        WHEN Stdnt.SEX = 'U' THEN 'Unknown'
        ELSE 'N/A'
    END AS Gender
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.SEX AS varchar(3)), 'N/A'))) AS GenderCode
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.ETHNICITY AS varchar(3)), 'N/A'))) AS EthnicityCode
  , LTRIM(RTRIM(ISNULL(Ethnic.[DESCRIPTION], 'N/A'))) AS Ethnicity
  , CASE
        WHEN Stdnt.FMLY_HIST_FLG = 'Y' THEN 'Yes'
        ELSE 'No'
    END AS FirstTimeInCollege
  , ISNULL(Race.White, 'N/A') AS RaceWhite
  , ISNULL(Race.BlackAfricanAmerican, 'N/A') AS RaceAfricanAmerican
  , ISNULL(Race.HawaiianPacIslander, 'N/A') AS RacePacificIslander
  , ISNULL(Race.Asian, 'N/A') AS RaceAsian
  , ISNULL(Race.AmIndianAKNative, 'N/A') AS RaceAmericanIndian
  , ISNULL(Race.NotReported, 'N/A') AS RaceUnreported
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.PRIM_LANG AS varchar(3)), 'N/A'))) AS PrimaryLanguageCode
  , LTRIM(RTRIM(ISNULL(Lang.LANGUAGE_DESCRIPTION, 'N/A'))) AS PrimaryLanguage
  , LTRIM(RTRIM(ISNULL(crdntl.INST_ID, 'N/A'))) AS HighSchoolInstitutionId
  , LTRIM(RTRIM(ISNULL(hsinst.INST_NM, 'N/A'))) AS HighSchool
  , LTRIM(RTRIM(ISNULL(Stdnt.HM_PHN, 'N/A'))) AS HomePhoneNumber
  , LTRIM(RTRIM(ISNULL(Stdnt.FAX_NUM, 'N/A'))) AS CellPhoneNumber
  , LTRIM(RTRIM(ISNULL(Stdnt.EMAIL_INST, 'N/A'))) AS InstitutionalEmailAddress
  , LTRIM(RTRIM(ISNULL(Stdnt.EMAIL_ADDR, 'N/A'))) AS PersonalEmailAddress
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.IMMIG_STAT AS varchar(3)), 'N/A'))) AS ImmigrationStatusCode
  , LTRIM(RTRIM(ISNULL(Immig.[DESCRIPTION], 'N/A'))) AS ImmigrationStatus
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.RES_CD AS varchar(3)), 'N/A'))) AS ResidencyCode
  , LTRIM(RTRIM(ISNULL(Res.[DESCRIPTION], 'N/A'))) AS Residency
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.RES_CNTY_CD AS varchar(3)), 'N/A'))) AS ResidencyCountyCode
  , LTRIM(RTRIM(ISNULL(County.COUNTY_DESCRIPTION, 'N/A'))) AS ResidencyCounty
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.RES_ST_CD AS varchar(3)), 'N/A'))) AS ResidencyStateCode
  , LTRIM(RTRIM(ISNULL(State.STATE_DESCRIPTION, 'N/A'))) AS ResidencyState
  , LTRIM(RTRIM(ISNULL(StdntAddr.ZIP_CD, 'N/A'))) AS ZipCode
  ,CASE
        WHEN ISDATE(Stdnt.DOB) = 1 THEN  dbo.udf_StringToDate(Stdnt.DOB)
        WHEN Stdnt.DOB IS NULL THEN dbo.udf_StringToDate('18000101')
        ELSE dbo.udf_StringToDate('18000102')
        END AS DateOfBirth
  , CAST(CASE
        WHEN ferpa.REST_DATE_AUTHORIZED <= @CurrentYYYYMMDD
         AND ISNULL(ferpa.REST_EXPIRATION_DATE, '21991231') >= @CurrentYYYYMMDD THEN 'Yes'
        ELSE 'No'
    END AS varchar(3)) AS FERPARequested
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.CNTRY_BIRTH AS varchar(3)), 'N/A'))) AS CountryOfBirthCode
  , LTRIM(RTRIM(ISNULL(Country.COUNTRY_DESCRIPTION, 'N/A'))) as CountryOfBirth
  , LTRIM(RTRIM(ISNULL(CAST(Stdnt.RES_CNTRY_CD AS varchar(3)), 'N/A'))) AS CountryOfCitizenshipCode
  , LTRIM(RTRIM(ISNULL(Citiz.COUNTRY_DESCRIPTION, 'N/A'))) AS CountryOfCitizenship
  , CASE
        WHEN Stdnt.CAMP_PREF IS NULL OR LTRIM(RTRIM(Stdnt.CAMP_PREF)) = '' THEN 'N/A'
        ELSE LEFT(Stdnt.CAMP_PREF,1)
    END AS HomeCampusCode
  , LTRIM(RTRIM(ISNULL(site.SITE_NAME, 'N/A'))) AS HomeCampus
  , CASE
        WHEN (EngPlcmnt.INIT_STATUS_IND = 'R' OR EngPlcmnt.INIT_STATUS_IND = 'U') AND
            (MatPlcmnt.INIT_STATUS_IND = 'R' OR MatPlcmnt.INIT_STATUS_IND = 'U') AND
            (ReaPlcmnt.INIT_STATUS_IND = 'R' OR ReaPlcmnt.INIT_STATUS_IND = 'U')
            THEN 'Unspecified'
        WHEN (EngPlcmnt.INIT_STATUS_IND = 'I' OR EngPlcmnt.INIT_STATUS_IND = 'E' OR EngPlcmnt.INIT_STATUS_IND = 'C') OR
           (MatPlcmnt.INIT_STATUS_IND = 'I' OR MatPlcmnt.INIT_STATUS_IND = 'E' OR MatPlcmnt.INIT_STATUS_IND = 'C') OR
           (ReaPlcmnt.INIT_STATUS_IND = 'I' OR ReaPlcmnt.INIT_STATUS_IND = 'E' OR ReaPlcmnt.INIT_STATUS_IND = 'C')
            THEN 'Required Prep'
        ELSE 'Did Not Require Prep'
    END AS InitialPrepStatus
  , CASE
        WHEN Stdnt.VET_FLG = 1 OR Vet.VA_BEN_STAT_CD = 'RC' THEN 'Yes'
        ELSE 'No'
    END AS VeteranStudent
  , CASE
        WHEN SD.SPCL_DSGNTR = 'AT' THEN 'Yes'
        ELSE 'No'
    END AS StudentAthlete
  , CASE
        WHEN immigDetail.FIELD_VALUE = 'Y' THEN 'Yes'
        ELSE 'No'
    END AS InternationalStudent
FROM ST_STDNT_A Stdnt
/* Get zip code from student address *//
LEFT OUTER JOIN ST_ADDRESSES_A StdntAddr
  ON (
      Stdnt.STUDENT_ID = StdntAddr.STUDENT_ID
      AND StdntAddr.ADDR_TY = '01'
     )
/* Get ethnicity code description *//
LEFT OUTER JOIN UTL_CODE_TABLE Ethnic
   ON (
       Ethnic.TABLE_NAME = 'ETHNIC'
       AND Ethnic.CODE = Stdnt.ETHNICITY
       AND Ethnic.[STATUS] = 'A'
      )
/* Get primary language code description *//
LEFT OUTER JOIN uvw_CODE_Language Lang
   ON (
       Stdnt.PRIM_LANG = Lang.CODE
      )

/* Get immigration code description *//
LEFT OUTER JOIN UTL_CODE_TABLE Immig
   ON (
        Immig.TABLE_NAME = 'ST-IMMIGRA'
        AND Immig.CODE = Stdnt.IMMIG_STAT
        AND Immig.[STATUS] = 'A'
       )
/* Get immigration code - international indicator *//
LEFT OUTER JOIN UTL_CODE_TABLE_GENERIC immigDetail
   ON (
        Immig.ISN_UTL_CODE_TABLE = immigDetail.ISN_UTL_CODE_TABLE
        AND immigDetail.cnxarraycolumn =     2/* Third sub-attribute of table */
       )
/* Get residency state code description *//
LEFT OUTER JOIN uvw_CODE_State State
   ON (
       Stdnt.RES_ST_CD = State.CODE
      )
/* Get residency code description *//
LEFT OUTER JOIN UTL_CODE_TABLE Res
    ON (
        Res.TABLE_NAME = 'RESIDENCY'
        AND Res.CODE = Stdnt.RES_CD
        AND Res.[STATUS] = 'A'
        )

/* Get residency county code description *//
LEFT OUTER JOIN uvw_CODE_County County
    ON (
        Stdnt.RES_CNTY_CD =  County.CODE
       )
/* Get country of birth code description *//
LEFT OUTER JOIN uvw_CODE_Country Country
    ON (
        Stdnt.CNTRY_BIRTH =  Country.CODE
       )

/* Get country of citizenship code description *//
LEFT OUTER JOIN uvw_CODE_Country Citiz
    ON (
        Stdnt.RES_CNTRY_CD =  Citiz.CODE
       )
/* Get race code description *//
Left OUTER JOIN uvw_StudentRace Race
    ON (
      Stdnt.ISN_ST_STDNT_A = Race.ISN_ST_STDNT_A
      )
/* Determine if student currently has FERPA */
LEFT OUTER JOIN ST_RESTRICTED_INFO_A ferpa
     ON (
         ferpa.STUDENT_ID = Stdnt.STUDENT_ID
         AND ferpa.REST_DATE_AUTHORIZED <= @CurrentYYYYMMDD
         AND ISNULL(ferpa.REST_EXPIRATION_DATE, '21991231') >= @CurrentYYYYMMDD
         )

/* Get graduated High School Id, if applicable *//
LEFT OUTER JOIN ST_EXTRNL_CRDNTL_A crdntl
    ON (
        crdntl.STDNT_ID = Stdnt.STUDENT_ID
        AND crdntl.CRDNTL_CD = 'HC'
        AND crdntl.ACT_GRAD_DT IS NOT NULL
        )

/* Get Institution Id name */
LEFT OUTER JOIN
    (
     SELECT DISTINCT inst1.INST_NM
                     ,inst1.INST_ID
                     ,inst1.INST_TY

     FROM ST_INSTITUTION_A inst1) AS hsinst
        ON (
            hsinst.INST_ID = crdntl.INST_ID
            AND hsinst.INST_TY = 'H'
            )
/* Determine Home Campus Site Physical *//
LEFT OUTER JOIN FAC192_SITE_A sitelocal
   ON (
       sitelocal.SITE_LOCAL_NUM = LEFT(Stdnt.CAMP_PREF,1)
       AND sitelocal.REC_TYPE = 'S'
       )
/* Get Home Campus - Site Name *//
LEFT OUTER JOIN FAC192_SITE_A site
   ON (
       site.SITE_NUM = sitelocal.SITE_NUM
       AND site.REC_TYPE = 'I'
       )
/* Get English Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A EngPlcmnt
   ON (
        EngPlcmnt.STDNT_ID = Stdnt.STUDENT_ID
        AND EngPlcmnt.PLACEMENT_AREA = 'ENG'
       )
/* Get Math Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A MatPlcmnt
   ON (
        MatPlcmnt.STDNT_ID = Stdnt.STUDENT_ID
        AND MatPlcmnt.PLACEMENT_AREA = 'MAT'
       )
/* Get Reading Placement */
LEFT OUTER JOIN ST_STDNT_TEST_DEMO_A ReaPlcmnt
   ON (
        ReaPlcmnt.STDNT_ID = Stdnt.STUDENT_ID
        AND ReaPlcmnt.PLACEMENT_AREA = 'REA'
       )
/* Get Veteran Details */
LEFT OUTER JOIN ST_VA_INT_A Vet
   ON (
        Vet.VA_H_STDNT_ID = Stdnt.STUDENT_ID
       )
/* Get Athlete Special Designator Details */
LEFT OUTER JOIN
    (SELECT DISTINCT STUDENT_ID, SPCL_DSGNTR FROM ST_STDNT_SPCL_DSGNTR_A) SD
    ON (
        SD.STUDENT_ID = Stdnt.STUDENT_ID
        and SD.SPCL_DSGNTR = 'AT'
       )
WHERE (Stdnt.STUDENT_ID) IN
(
/* Select changed records from Student File */
SELECT STUDENT_ID
FROM ST_STDNT_A
WHERE (
       ST_STDNT_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND ST_STDNT_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select changed records from Student Address File */
SELECT STUDENT_ID
FROM ST_ADDRESSES_A
WHERE (
       ST_ADDRESSES_A.ADDR_TY = '01'
       AND ST_ADDRESSES_A.RowUpdatedOn >= @IncrementalBeginDtTm
       AND ST_ADDRESSES_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select changed High School records */
SELECT STDNT_ID
FROM ST_EXTRNL_CRDNTL_A
WHERE (
        ST_EXTRNL_CRDNTL_A.CRDNTL_CD = 'HC'
        AND ST_EXTRNL_CRDNTL_A.ACT_GRAD_DT IS NOT NULL
        AND ST_EXTRNL_CRDNTL_A.RowUpdatedOn >= @IncrementalBeginDtTm
        AND ST_EXTRNL_CRDNTL_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select changed Veteran records */
SELECT VA_H_STDNT_ID
FROM ST_VA_INT_A
WHERE (
        ST_VA_INT_A.RowUpdatedOn >= @IncrementalBeginDtTm
        AND ST_VA_INT_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Select changed Athlete records */
SELECT STUDENT_ID
FROM ST_STDNT_SPCL_DSGNTR_A
WHERE (
        ST_STDNT_SPCL_DSGNTR_A.SPCL_DSGNTR = 'AT'
        AND ST_STDNT_SPCL_DSGNTR_A.RowUpdatedOn >= @IncrementalBeginDtTm
        AND ST_STDNT_SPCL_DSGNTR_A.RowUpdatedOn <= @IncrementalEndDtTm
      )
UNION
/* Get changed Institution names */
SELECT DISTINCT (ST_EXTRNL_CRDNTL_A.STDNT_ID)
FROM ST_INSTITUTION_A
INNER JOIN ST_EXTRNL_CRDNTL_A
   ON (
        ST_INSTITUTION_A.INST_ID = ST_EXTRNL_CRDNTL_A.INST_ID
       )
   WHERE (
           ST_INSTITUTION_A.INST_TY = 'H'
           AND ST_INSTITUTION_A.RowUpdatedOn >= @IncrementalBeginDtTm
           AND ST_INSTITUTION_A.RowUpdatedOn <= @IncrementalEndDtTm
          )
UNION
/* Select FERPA restriction changed records *//
SELECT STUDENT_ID
FROM ST_RESTRICTED_INFO_A
WHERE (
        ST_RESTRICTED_INFO_A.RowUpdatedOn >= @IncrementalBeginDtTm
        AND ST_RESTRICTED_INFO_A.RowUpdatedOn <= @IncrementalEndDtTm
      )

)

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database.
This Stored Procedure pulls information from the following tables:
ST_STDNT
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_DimStudent_Select'

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database.
This Stored Procedure pulls information from the following tables:
ST_STDNT
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_DimStudent_Select'

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database.
This Stored Procedure pulls information from the following tables:
ST_STDNT
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_DimStudent_Select'

EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'The Stored Procedure is used by the ETL process to extract all the Student Information from the DWStaging database.
This Stored Procedure pulls information from the following tables:
ST_STDNT
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'usp_DimStudent_Select'