V_QUERY_VOLUNTEERTRAITS
This provides the ability to query all volunteer traits in a consolidated view.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| VOLUNTEERID | uniqueidentifier | VOLUNTEERID | |
| TRAITTYPE | nvarchar(20) | yes | Trait type |
| DESCRIPTION | nvarchar(100) | Description | |
| SECONDARY | nvarchar(100) | yes | Number |
| SKILLLEVEL_TRANSLATION | nvarchar(25) | yes | Skill level |
| EXPIRESON | datetime | yes | Expires on |
| VERIFIED | bit | yes | Verified? |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:37:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_VOLUNTEERTRAITS AS
select VA.ID,
VA.VOLUNTEERID,
cast('Administrative' as nvarchar(20)) [TRAITTYPE],
ADMINISTRATIVE.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
VA.EXPIRESON ,
VA.VERIFIED,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VA.DATEADDED,
VA.DATECHANGED,
VA.TSLONG
from VOLUNTEERADMINISTRATIVE VA
inner join VOLUNTEERADMINISTRATIVECODE ADMINISTRATIVE on ADMINISTRATIVE.ID = VA.ADMINISTRATIVECODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VA.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VA.CHANGEDBYID
union all
select VC.ID,
VC.VOLUNTEERID,
cast('Certification' as nvarchar(20)) [TRAITTYPE],
CERTIFICATION.DESCRIPTION,
VC.CERTIFICATE [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
VC.EXPIRESON,
VERIFIED,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VC.DATEADDED,
VC.DATECHANGED,
VC.TSLONG
from VOLUNTEERCERTIFICATION VC
inner join VOLUNTEERCERTIFICATIONCODE CERTIFICATION on CERTIFICATION.ID = VC.CERTIFICATIONCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VC.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VC.CHANGEDBYID
union all
select VC.ID,
VC.VOLUNTEERID,
cast('Course' as nvarchar(20)) [TRAITTYPE],
COURSE.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
VC.EXPIRESON,
VERIFIED,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VC.DATEADDED,
VC.DATECHANGED,
VC.TSLONG
from VOLUNTEERCOURSE VC
inner join VOLUNTEERCOURSECODE COURSE on COURSE.ID = VC.COURSECODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VC.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VC.CHANGEDBYID
union all
select VI.ID,
VI.VOLUNTEERID,
cast('Interest' as nvarchar(20)) [TRAITTYPE],
INTEREST.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
cast(null as datetime) [EXPIRESON],
cast(null as bit) [VERIFIED],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VI.DATEADDED,
VI.DATECHANGED,
VI.TSLONG
from VOLUNTEERINTEREST VI
inner join VOLUNTEERINTERESTCODE INTEREST on INTEREST.ID = VI.VOLUNTEERINTERESTCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VI.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VI.CHANGEDBYID
union all
select VL.ID,
VL.VOLUNTEERID,
cast('License' as nvarchar(20)) [TRAITTYPE],
LICENSE.DESCRIPTION,
VL.LICENSENUMBER [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
VL.EXPIRESON,
VERIFIED,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VL.DATEADDED,
VL.DATECHANGED,
VL.TSLONG
from VOLUNTEERLICENSE VL
inner join VOLUNTEERLICENSECODE LICENSE on LICENSE.ID = VL.LICENSECODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VL.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VL.CHANGEDBYID
union all
select VM.ID,
VM.VOLUNTEERID,
cast('Medical' as nvarchar(20)) [TRAITTYPE],
MEDICAL.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
VM.EXPIRESON,
VERIFIED,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VM.DATEADDED,
VM.DATECHANGED,
VM.TSLONG
from VOLUNTEERMEDICAL VM
inner join VOLUNTEERMEDICALCODE MEDICAL on MEDICAL.ID = VM.MEDICALCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VM.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VM.CHANGEDBYID
union all
select VS.ID,
VS.VOLUNTEERID,
cast('Skill' as nvarchar(20)) [TRAITTYPE],
SKILL.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
SKILLLEVEL.DESCRIPTION [SKILLLEVEL_TRANSLATION],
cast(null as datetime) [EXPIRESON],
cast(null as bit) [VERIFIED],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VS.DATEADDED,
VS.DATECHANGED,
VS.TSLONG
from VOLUNTEERSKILL VS
inner join VOLUNTEERSKILLCODE SKILL on SKILL.ID = VS.SKILLCODEID
inner join VOLUNTEERSKILLLEVEL SKILLLEVEL on SKILLLEVEL.ID = VS.SKILLLEVELID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VS.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VS.CHANGEDBYID
union all
select VS.ID,
VS.VOLUNTEERID,
cast('Special Need' as nvarchar(20)) [TRAITTYPE],
SPECIALNEED.DESCRIPTION,
cast(null as nvarchar(100)) [SECONDARY],
cast(null as nvarchar(25)) [SKILLLEVEL_TRANSLATION],
cast(null as datetime) [EXPIRESON],
cast(null as bit) [VERIFIED],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
VS.DATEADDED,
VS.DATECHANGED,
VS.TSLONG
from VOLUNTEERSPECIALNEED VS
inner join VOLUNTEERSPECIALNEEDCODE SPECIALNEED on SPECIALNEED.ID = VS.SPECIALNEEDCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VS.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VS.CHANGEDBYID