V_QUERY_HOUSEHOLD
Provides the ability to query household fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| STARTDATE | datetime | yes | Household consolidation date |
| DESCRIPTION | nvarchar(300) | Description | |
| CANBEDONOR | bit | yes | Can be donor |
| 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 | |
| DISSOLVED | bit | yes | Dissolved |
| DISSOLVEDDATE | datetime | yes | Dissolved date |
| DISSOLVEREASONCODEID_TRANSLATION | nvarchar(100) | yes | Dissolved reason |
| DISSOLVEREASONCODEID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:47 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_HOUSEHOLD AS
select
GD.ID,
GD.STARTDATE,
GD.DESCRIPTION,
dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS() CANBEDONOR,
ADDEDBY.APPLICATIONNAME as ADDEDBY_APPLICATION,
ADDEDBY.USERNAME as ADDEDBY_USERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBY_APPLICATION,
CHANGEDBY.USERNAME as CHANGEDBY_USERNAME,
GD.DATEADDED,
GD.DATECHANGED,
convert(bit, case when DG.ID is null then 0 else 1 end) as DISSOLVED,
DG.DISSOLVEDDATE,
(select DISSOLVEREASONCODE.DESCRIPTION from dbo.DISSOLVEREASONCODE where DISSOLVEREASONCODE.ID = DG.DISSOLVEREASONCODEID) as [DISSOLVEREASONCODEID_TRANSLATION],
[DG].[DISSOLVEREASONCODEID]
from
dbo.GROUPDATA GD
left outer join
dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = GD.ADDEDBYID
left outer join
dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = GD.CHANGEDBYID
left join
dbo.DISSOLVEDGROUP DG on GD.ID = DG.ID
where
GD.GROUPTYPECODE = 0