V_QUERY_HOUSEHOLDMEMBERSHIP
Provides the ability to query households a constituent belongs to.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| MEMBERID | uniqueidentifier | Member Record ID | |
| GROUPID | uniqueidentifier | Household Record ID | |
| ISCURRENT | int | Is current | |
| 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 | |
| ISPRIMARY | bit |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 11/11/2014 4:09:23 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLDMEMBERSHIP AS
select distinct
GM.ID,
GM.MEMBERID,
GM.GROUPID,
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
(case when ((GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > cast(getdate() as date)))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= cast(getdate() as date)))
or (GMDR.DATEFROM <= cast(getdate() as date) and GMDR.DATETO > cast(getdate() as date)))
then 1
else 0
end) as ISCURRENT,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
GM.DATEADDED,
GM.DATECHANGED,
GM.ISPRIMARY
from dbo.GROUPMEMBER GM
left outer join dbo.GROUPDATA GD on GM.GROUPID = GD.ID
left outer join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
left outer join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = GM.ADDEDBYID
left outer join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = GM.CHANGEDBYID
where GD.GROUPTYPECODE = 0