V_QUERY_EDUCATIONALHISTORYUPDATEBATCHCONTROLREPORT
View used for output for educational history update batch control report.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTNAME | nvarchar(700) | yes | Constituent name |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| INSTITUTION | nvarchar(100) | yes | Institution |
| PROGRAM | nvarchar(100) | yes | Program |
| DEGREE | nvarchar(150) | yes | Degree |
| COLLEGE | nvarchar(150) | yes | College/school |
| DIVISION | nvarchar(150) | yes | Division |
| DEPARTMENT | nvarchar(150) | yes | Department |
| SUBDEPARTMENT | nvarchar(150) | yes | Sub department |
| DEGREETYPE | nvarchar(100) | yes | Degree type |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 2/1/2011 9:03:03 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.9.1001.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EDUCATIONALHISTORYUPDATEBATCHCONTROLREPORT AS
select
EH.[ID],
dbo.UFN_NAMEFORMATFUNCTION_SIMPLE(C.ID, C.KEYNAME, C.FIRSTNAME, C.MIDDLENAME, (select DESCRIPTION from dbo.TITLECODE where ID = C.TITLECODEID), (select DESCRIPTION from dbo.SUFFIXCODE where ID = C.SUFFIXCODEID),'','','','','') as CONSTITUENTNAME,
C.[LOOKUPID],
EI.[NAME] as INSTITUTION,
case
when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACP.PROGRAM
else EPC.[DESCRIPTION]
end as PROGRAM,
case
when ECON.USEACADEMICCATALOG = 1 and EI.ISAFFILIATED = 1 then ACD.NAME
else EDC.[DESCRIPTION]
end as DEGREE,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACC.NAME
else ECC.DESCRIPTION
end as COLLEGE,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDIV.NAME
else EDIV.DESCRIPTION
end as DIVISION,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDEPT.NAME
else EDEPT.DESCRIPTION
end as DEPARTMENT,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACSUBDEPT.NAME
else ESUBDEPT.DESCRIPTION
end as SUBDEPARTMENT,
case
when (ECON.USEACADEMICCATALOG & EI.ISAFFILIATED) = 1 then ACDTC.DESCRIPTION
else EDTC.DESCRIPTION
end as DEGREETYPE
from
dbo.[EDUCATIONALHISTORY] EH
left join
dbo.[EDUCATIONALINSTITUTION] EI on EI.[ID] = EH.[EDUCATIONALINSTITUTIONID]
left join
dbo.[EDUCATIONALPROGRAMCODE] EPC on EPC.[ID] = EH.[EDUCATIONALPROGRAMCODEID]
left join
dbo.[EDUCATIONALDEGREECODE] EDC on EDC.[ID] = EH.[EDUCATIONALDEGREECODEID]
left join
dbo.[ACADEMICCATALOGPROGRAM] ACP on ACP.[ID] = EH.[ACADEMICCATALOGPROGRAMID]
left join
dbo.[ACADEMICCATALOGDEGREE] ACD on ACD.ID = EH.[ACADEMICCATALOGDEGREEID]
left join
dbo.EDUCATIONADDITIONALINFORMATION EAI on EAI.ID = (select top(1) ID from dbo.EDUCATIONADDITIONALINFORMATION where EDUCATIONALHISTORYID = EH.ID order by TSLONG desc)
left join
dbo.EDUCATIONALCOLLEGECODE ECC on ECC.ID = EAI.EDUCATIONALCOLLEGECODEID
left join
dbo.EDUCATIONALDIVISIONCODE EDIV on EDIV.ID = EAI.EDUCATIONALDIVISIONCODEID
left join
dbo.EDUCATIONALDEPARTMENTCODE EDEPT on EDEPT.ID = EAI.EDUCATIONALDEPARTMENTCODEID
left join
dbo.EDUCATIONALSUBDEPARTMENTCODE ESUBDEPT on ESUBDEPT.ID = EAI.EDUCATIONALSUBDEPARTMENTCODEID
left join
dbo.EDUCATIONALDEGREETYPECODE EDTC on EDTC.ID = EAI.EDUCATIONALDEGREETYPECODEID
left join
dbo.ACADEMICCATALOGCOLLEGE ACC on ACC.ID = EAI.ACADEMICCATALOGCOLLEGEID
left join
dbo.ACADEMICCATALOGDIVISION ACDIV on ACDIV.ID = EAI.ACADEMICCATALOGDIVISIONID
left join
dbo.ACADEMICCATALOGDEPARTMENT ACDEPT on ACDEPT.ID = EAI.ACADEMICCATALOGDEPARTMENTID
left join
dbo.ACADEMICCATALOGSUBDEPARTMENT ACSUBDEPT on ACSUBDEPT.ID = EAI.ACADEMICCATALOGSUBDEPARTMENTID
left join
dbo.ACADEMICCATALOGDEGREETYPE ACDT on ACDT.ID = EAI.ACADEMICCATALOGDEGREETYPEID
left join
dbo.ACADEMICCATALOGDEGREETYPECODE ACDTC on ACDTC.ID = ACDT.ACADEMICCATALOGDEGREETYPECODEID
left join
dbo.[EDUCATIONALCONFIGURATION] ECON on 1 = 1
left join
dbo.[CONSTITUENT] C on C.[ID] = EH.[CONSTITUENTID]