V_QUERY_EVENTHIERARCHYBYREGISTRANT
Query to relate a registrant to all events in a multi-level event and other registrant records for this constituent.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | Registrant ID being queried | |
| MAINEVENTID | uniqueidentifier | Main Event ID | |
| SUBEVENTID | uniqueidentifier | Event ID | |
| LEVEL | smallint | yes | Level |
| LEVELNAME | nvarchar(100) | yes | Level name |
| REGISTRANTINSUBEVENTID | uniqueidentifier | yes | Registrant ID |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:44:55 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_EVENTHIERARCHYBYREGISTRANT AS
select
REGISTRANTQUERIED.ID,
EVENTHIERARCHYMAIN.ID as MAINEVENTID,
EVENTHIERARCHYSUB.ID as SUBEVENTID,
EVENTHIERARCHYSUB.HIERARCHYPATH.GetLevel() LEVEL,
coalesce(EVENTMANAGEMENTLEVELINSTANCE.NAME, '') LEVELNAME,
REGISTRANT.ID as REGISTRANTINSUBEVENTID
from
dbo.REGISTRANT as REGISTRANTQUERIED
inner join dbo.EVENTHIERARCHY as EVENTHIERARCHYQUERIED on REGISTRANTQUERIED.EVENTID = EVENTHIERARCHYQUERIED.ID
inner join dbo.EVENTHIERARCHY as EVENTHIERARCHYMAIN on EVENTHIERARCHYQUERIED.HIERARCHYPATH.IsDescendantOf(EVENTHIERARCHYMAIN.HIERARCHYPATH) = 1
inner join dbo.EVENTHIERARCHY as EVENTHIERARCHYSUB on EVENTHIERARCHYSUB.HIERARCHYPATH.IsDescendantOf(EVENTHIERARCHYMAIN.HIERARCHYPATH) = 1
inner join dbo.EVENTMANAGEMENTOPTIONS on EVENTMANAGEMENTOPTIONS.EVENTID = EVENTHIERARCHYSUB.ID
left outer join dbo.EVENTMANAGEMENTLEVELINSTANCE on EVENTMANAGEMENTLEVELINSTANCE.ID = EVENTMANAGEMENTOPTIONS.EVENTMANAGEMENTLEVELINSTANCEID
left outer join dbo.REGISTRANT on EVENTHIERARCHYSUB.ID = REGISTRANT.EVENTID and REGISTRANT.CONSTITUENTID = REGISTRANTQUERIED.CONSTITUENTID