V_QUERY_HOUSEHOLDMEMBER
Provides the ability to query household member fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System Record ID | |
| MEMBERID | uniqueidentifier | Member Record ID | |
| GROUPID | uniqueidentifier | Household Record ID | |
| ISFORMERMEMBER | int | Is former member | |
| ISPRIMARY | bit | Is primary contact | |
| 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 |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:42:18 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLDMEMBER 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 0
else 1
end) as ISFORMERMEMBER,
GM.ISPRIMARY,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
GM.DATEADDED,
GM.DATECHANGED
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