V_QUERY_EDUCATIONALHISTORY
This provides the ability to query constituent educational history information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System Record ID |
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| EDUCATIONALINSTITUTIONID | uniqueidentifier | yes | Educational institution ID |
| EDUCATIONALINSTITUTIONNAME | nvarchar(154) | yes | Educational institution name |
| ISPRIMARYRECORD | bit | yes | Primary education information |
| KNOWNNAME | nvarchar(50) | yes | Known name |
| TYPE | nvarchar(100) | yes | Type |
| STATUS | nvarchar(100) | yes | Detail |
| CONSTITUENCYSTATUS | nvarchar(19) | yes | Constituency status |
| DEGREE | nvarchar(100) | yes | Educational degree |
| STARTDATE | UDT_FUZZYDATE | yes | Date attended from |
| ENDDATE | UDT_FUZZYDATE | yes | End date |
| DATEGRADUATED | UDT_FUZZYDATE | yes | Graduation date |
| DATELEFT | UDT_FUZZYDATE | yes | Date attended to |
| GPA | decimal(3, 2) | yes | GPA |
| CLASSOF | UDT_YEAR | yes | Class of |
| FRATERNITY | nvarchar(50) | yes | Frat/Sorority |
| ADDEDBY_APPLICATION | nvarchar(200) | Added by application | |
| ADDEDBY_USERNAME | nvarchar(128) | Added by user name | |
| CHANGEDBY_APPLICATION | nvarchar(200) | Changed by application | |
| CHANGEDBY_USERNAME | nvarchar(128) | Changed by user name | |
| DATEADDED | datetime | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| ACADEMICCATALOGPROGRAM | nvarchar(13) | yes | Academic catalog program |
| EDUCATIONALPROGRAM | nvarchar(100) | yes | Educational program |
| ACADEMICCATALOGDEGREEID | nvarchar(50) | yes | Academic catalog degree ID |
| ACADEMICCATALOGDEGREE | nvarchar(150) | yes | Academic catalog degree |
| AWARDED | nvarchar(100) | yes | Honor awarded |
| PREFERREDCLASSYEAR | UDT_YEAR | yes | Preferred class of |
| SOURCE | nvarchar(100) | yes | Information source |
| EDUCATIONALSOURCEDATE | UDT_FUZZYDATE | yes | Source date |
| ACADEMICCATALOGDEGREEISHISTORICAL | bit | yes | Academic catalog degree is historical |
| EDUCATIONALHISTORYLEVELCODE | nvarchar(100) | yes | Level |
| EDUCATIONALHISTORYREASONCODE | nvarchar(100) | yes | Reason |
| COMMENT | nvarchar(500) | yes | Comments |
| EDUCATIONALHISTORYSTATUS | nvarchar(100) | yes | Status |
| DEGREE_SHORTCODE | nvarchar(25) | yes | Educational degree short code |
| DEGREE_LONGDESCRIPTION | nvarchar(250) | yes | Educational degree long description |
| INSTITUTIONNAME | nvarchar(154) | yes | |
| EDUCATIONALHISTORYSTATUSID | uniqueidentifier | yes | |
| EDUCATIONALPROGRAMID | uniqueidentifier | yes | |
| INSTITUTIONID | uniqueidentifier | yes | |
| EDUCATIONALHISTORYTYPECODEID | uniqueidentifier | yes | |
| EDUCATIONALAWARDCODEID | uniqueidentifier | yes | |
| EDUCATIONALDEGREECODEID | uniqueidentifier | yes | |
| EDUCATIONALHISTORYLEVELCODEID | uniqueidentifier | yes | |
| EDUCATIONALHISTORYREASONCODEID | uniqueidentifier | yes | |
| EDUCATIONALSOURCECODEID | uniqueidentifier | yes | |
| EDUCATIONALHISTORYSTATUSCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:00:10 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EDUCATIONALHISTORY AS
select
HISTORY.ID,
HISTORY.CONSTITUENTID as CONSTITUENTID,
HISTORY.EDUCATIONALINSTITUTIONID,
INSTITUTION.NAME as EDUCATIONALINSTITUTIONNAME,
HISTORY.ISPRIMARYRECORD,
HISTORY.KNOWNNAME,
TYPE.DESCRIPTION as TYPE,
STATUS.DESCRIPTION as STATUS,
CONSTITUENCYSTATUS,
DEGREE.DESCRIPTION as DEGREE,
HISTORY.STARTDATE,
HISTORY.ENDDATE,
HISTORY.DATEGRADUATED,
HISTORY.DATELEFT,
HISTORY.GPA,
HISTORY.CLASSOF,
HISTORY.FRATERNITY,
isnull(ADDEDBY.APPLICATIONNAME,'') as ADDEDBY_APPLICATION,
isnull(ADDEDBY.USERNAME,'') as ADDEDBY_USERNAME,
isnull(CHANGEDBY.APPLICATIONNAME,'') as CHANGEDBY_APPLICATION,
isnull(CHANGEDBY.USERNAME,'') as CHANGEDBY_USERNAME,
HISTORY.DATEADDED,
HISTORY.DATECHANGED,
HISTORY.TSLONG,
ACADEMICCATALOGPROGRAM.PROGRAM as ACADEMICCATALOGPROGRAM,
EDUCATIONALPROGRAM.DESCRIPTION as EDUCATIONALPROGRAM,
ACADEMICCATALOGDEGREE.USERID as ACADEMICCATALOGDEGREEID,
ACADEMICCATALOGDEGREE.NAME as ACADEMICCATALOGDEGREE,
AWARD.DESCRIPTION as AWARDED,
HISTORY.PREFERREDCLASSYEAR,
SOURCE.DESCRIPTION as SOURCE,
HISTORY.EDUCATIONALSOURCEDATE,
ACADEMICCATALOGDEGREE.ISHISTORICAL as ACADEMICCATALOGDEGREEISHISTORICAL,
LEVEL.DESCRIPTION as EDUCATIONALHISTORYLEVELCODE,
REASON.DESCRIPTION as EDUCATIONALHISTORYREASONCODE,
HISTORY.COMMENT as COMMENT,
EDUCATIONALHISTORYSTATUS.DESCRIPTION as EDUCATIONALHISTORYSTATUS,
DEGREEEXT.SHORTCODE DEGREE_SHORTCODE,
DEGREEEXT.LONGDESCRIPTION DEGREE_LONGDESCRIPTION,
INSTITUTION.NAME as INSTITUTIONNAME,
[EDUCATIONALHISTORYSTATUS].[ID] as [EDUCATIONALHISTORYSTATUSID],
[EDUCATIONALPROGRAM].[ID] as [EDUCATIONALPROGRAMID],
[INSTITUTION].[ID] as [INSTITUTIONID],
[TYPE].[ID] as [EDUCATIONALHISTORYTYPECODEID],
[AWARD].[ID] as [EDUCATIONALAWARDCODEID],
[DEGREE].[ID] as [EDUCATIONALDEGREECODEID],
[LEVEL].[ID] as [EDUCATIONALHISTORYLEVELCODEID],
[REASON].[ID] as [EDUCATIONALHISTORYREASONCODEID],
[SOURCE].[ID] as [EDUCATIONALSOURCECODEID],
[STATUS].[ID] as [EDUCATIONALHISTORYSTATUSCODEID]
/*#EXTENSION*/
from dbo.CONSTITUENT
left join dbo.EDUCATIONALHISTORY as HISTORY on CONSTITUENT.ID = HISTORY.CONSTITUENTID
left join dbo.EDUCATIONALHISTORYTYPECODE as TYPE on HISTORY.EDUCATIONALHISTORYTYPECODEID = TYPE.ID
left join dbo.EDUCATIONALHISTORYSTATUSCODE as STATUS on HISTORY.EDUCATIONALHISTORYSTATUSCODEID = STATUS.ID
outer apply dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2(HISTORY.ID) as CURRENTEDUCATIONALHISTORYSTATUS
left join dbo.EDUCATIONALHISTORYSTATUS on CURRENTEDUCATIONALHISTORYSTATUS.EDUCATIONALHISTORYSTATUSID = EDUCATIONALHISTORYSTATUS.ID
left join dbo.EDUCATIONALDEGREECODE as DEGREE on HISTORY.EDUCATIONALDEGREECODEID = DEGREE.ID
left join dbo.CONSTITUENT as INSTITUTION on HISTORY.EDUCATIONALINSTITUTIONID = INSTITUTION.ID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = HISTORY.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = HISTORY.CHANGEDBYID
left join dbo.ACADEMICCATALOGPROGRAM on HISTORY.ACADEMICCATALOGPROGRAMID = ACADEMICCATALOGPROGRAM.ID
left join dbo.ACADEMICCATALOGDEGREE on HISTORY.ACADEMICCATALOGDEGREEID = ACADEMICCATALOGDEGREE.ID
left join dbo.EDUCATIONALPROGRAMCODE as EDUCATIONALPROGRAM on HISTORY.EDUCATIONALPROGRAMCODEID = EDUCATIONALPROGRAM.ID
left join dbo.EDUCATIONALAWARDCODE as AWARD on HISTORY.EDUCATIONALAWARDCODEID = AWARD.ID
left join dbo.EDUCATIONALSOURCECODE as SOURCE on HISTORY.EDUCATIONALSOURCECODEID = SOURCE.ID
left join dbo.EDUCATIONALHISTORYLEVELCODE as LEVEL on HISTORY.EDUCATIONALHISTORYLEVELCODEID = LEVEL.ID
left join dbo.EDUCATIONALHISTORYREASONCODE as REASON on HISTORY.EDUCATIONALHISTORYREASONCODEID = REASON.ID
left join dbo.EDUCATIONALDEGREECODEEXTENSION DEGREEEXT on DEGREE.ID = DEGREEEXT.ID