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