Derived Columns for "stub" - RowIsDuplicate RowIsSelected columns are added

OleDbConnection

AccessMode

3

AlwaysUseDefaultCodePage

False

CommandTimeout

0

DefaultCodePage

1252

OpenRowset

OpenRowsetVariable

ParameterMapping

SqlCommand

DECLARE @IncrementalBeginDtTm datetime  
DECLARE @IncrementalEndDtTm datetime  
DECLARE @CurrentDayDate datetime 
DECLARE @Notime date
SET @Notime = CAST(@CurrentDayDate AS DATE)
DECLARE @CurrentYYYYMMDD varchar(8)
DECLARE @TempDate varchar(10)
/* Convert Date yyyy-mm-dd to yyyymmdd */
--SET @TempDate = CAST(@CurrentDayDate AS varchar(10))
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 
  , Stdnt.LST_NM AS LastName
  , Stdnt.FRST_NM AS FirstName
  , 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 
  , ISNULL(CAST(Stdnt.SEX AS varchar(3)), 'N/A') AS GenderCode
  , ISNULL(CAST(Stdnt.ETHNICITY AS varchar(3)), 'N/A') AS EthnicityCode
  , 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
  , ISNULL(CAST(Stdnt.PRIM_LANG AS varchar(3)), 'N/A') AS PrimaryLanguageCode   
  , ISNULL(Lang.LANGUAGE_DESCRIPTION, 'N/A') AS PrimaryLanguage
  , ISNULL(crdntl.INST_ID, 'N/A') AS HighSchoolInstitutionId
  , ISNULL(hsinst.INST_NM, 'N/A') AS HighSchool  
  , ISNULL(Stdnt.HM_PHN, 'N/A') AS HomePhoneNumber
  , ISNULL(Stdnt.FAX_NUM, 'N/A') AS CellPhoneNumber 
  , ISNULL(Stdnt.EMAIL_INST, 'N/A') AS InstitutionalEmailAddress
  , ISNULL(Stdnt.EMAIL_ADDR, 'N/A') AS PersonalEmailAddress 
  , ISNULL(CAST(Stdnt.IMMIG_STAT AS varchar(3)), 'N/A') AS ImmigrationStatusCode
  , ISNULL(Immig.[DESCRIPTION], 'N/A') AS ImmigrationStatus
  , ISNULL(CAST(Stdnt.RES_CD AS varchar(3)), 'N/A') AS ResidencyCode
  , ISNULL(Res.[DESCRIPTION], 'N/A') AS Residency
  , ISNULL(CAST(Stdnt.RES_CNTY_CD AS varchar(3)), 'N/A') AS ResidencyCountyCode
  , ISNULL(County.COUNTY_DESCRIPTION, 'N/A') AS ResidencyCounty  
  , ISNULL(CAST(Stdnt.RES_ST_CD AS varchar(3)), 'N/A') AS ResidencyStateCode
  , ISNULL(State.STATE_DESCRIPTION, 'N/A') AS ResidencyState 
  , ISNULL(StdntAddr.ZIP_CD, 'N/A') AS ZipCode
  , dbo.udf_StringToDate(ISNULL(Stdnt.DOB, '18000101')) 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  
  , ISNULL(CAST(Stdnt.CNTRY_BIRTH AS varchar(3)), 'N/A') AS CountryOfBirthCode
  , ISNULL(Country.COUNTRY_DESCRIPTION, 'N/A') as CountryOfBirth
  , ISNULL(CAST(Stdnt.RES_CNTRY_CD AS varchar(3)), 'N/A') AS CountryOfCitizenshipCode
  , ISNULL(Citiz.COUNTRY_DESCRIPTION, 'N/A') AS CountryOfCitizenship
   
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 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'
            )
   
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
/* 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
      )      
  
)

SqlCommandVariable

User::GetStudentMods

CellPhoneNumber

678

678

CountryOfBirth

708

708

CountryOfBirthCode

706

706

CountryOfCitizenship

712

712

CountryOfCitizenshipCode

710

710

DateOfBirth

702

702

ErrorCode

13

13

ErrorColumn

14

14

Ethnicity

652

652

EthnicityCode

650

650

FERPARequested

704

704

FirstName

640

640

FirstTimeInCollege

654

654

Fullname

644

644

Gender

648

648

GenderCode

646

646

HighSchool

674

674

HighSchoolInstitutionId

672

672

HomeCampus

1035

1035

HomeCampusCode

1032

1032

HomePhoneNumber

676

676

ImmigrationStatus

686

686

ImmigrationStatusCode

684

684

InitialPrepStatus

968

968

InstitutionalEmailAddress

680

680

InternationalStudent

977

977

LastName

638

638

MiddleName

642

642

PersonalEmailAddress

682

682

PrimaryLanguage

670

670

PrimaryLanguageCode

668

668

RaceAfricanAmerican

658

658

RaceAmericanIndian

664

664

RaceAsian

662

662

RacePacificIslander

660

660

RaceUnreported

666

666

RaceWhite

656

656

Residency

690

690

ResidencyCode

688

688

ResidencyCounty

694

694

ResidencyCountyCode

692

692

ResidencyState

698

698

ResidencyStateCode

696

696

StudentAthlete

974

974

StudentId

636

636

StudentIdAK

634

634

VeteranStudent

971

971

ZipCode

700

700

CellPhoneNumber

677

677

CountryOfBirth

707

707

CountryOfBirthCode

705

705

CountryOfCitizenship

711

711

CountryOfCitizenshipCode

709

709

DateOfBirth

701

701

Ethnicity

651

651

EthnicityCode

649

649

FERPARequested

703

703

FirstName

639

639

FirstTimeInCollege

653

653

Fullname

643

643

Gender

647

647

GenderCode

645

645

HighSchool

673

673

HighSchoolInstitutionId

671

671

HomeCampus

1034

1034

HomeCampusCode

1031

1031

HomePhoneNumber

675

675

ImmigrationStatus

685

685

ImmigrationStatusCode

683

683

InitialPrepStatus

967

967

InstitutionalEmailAddress

679

679

InternationalStudent

976

976

LastName

637

637

MiddleName

641

641

PersonalEmailAddress

681

681

PrimaryLanguage

669

669

PrimaryLanguageCode

667

667

RaceAfricanAmerican

657

657

RaceAmericanIndian

663

663

RaceAsian

661

661

RacePacificIslander

659

659

RaceUnreported

665

665

RaceWhite

655

655

Residency

689

689

ResidencyCode

687

687

ResidencyCounty

693

693

ResidencyCountyCode

691

691

ResidencyState

697

697

ResidencyStateCode

695

695

StudentAthlete

973

973

StudentId

635

635

StudentIdAK

633

633

VeteranStudent

970

970

ZipCode

699

699