V_QUERY_CONSTITUENCY
This provides the ability to query constituency information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System Record ID |
| CONSTITUENTID | uniqueidentifier | yes | Constituent ID |
| CONSTITUENCY | nvarchar(100) | yes | Constituency |
| DATEFROM | datetime | yes | Date from |
| DATETO | date | yes | Date to |
| 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 | yes | Date added |
| DATECHANGED | datetime | yes | Date changed |
| TSLONG | bigint | yes | Timestamp value |
| CONSTITUENCYDEFINITIONID | uniqueidentifier | ||
| ISACTIVE | bit |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 5/3/2024 2:17:05 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENCY AS
select
CONSTITUENCY.ID,
CONSTITUENCY.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYCODE.DESCRIPTION [CONSTITUENCY],
CONSTITUENCY.DATEFROM,
CONSTITUENCY.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
CONSTITUENCY.DATEADDED,
CONSTITUENCY.DATECHANGED,
CONSTITUENCY.TSLONG,
CONSTITUENCY.CONSTITUENCYCODEID as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
/*#EXTENSION*/
from dbo.CONSTITUENCY
left join dbo.CONSTITUENCYCODE on CONSTITUENCYCODE.ID = CONSTITUENCY.CONSTITUENCYCODEID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = CONSTITUENCY.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = CONSTITUENCY.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCY.CONSTITUENCYCODEID
union all
--Board member
select
BOARDMEMBERDATERANGE.ID,
BOARDMEMBERDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
BOARDMEMBERDATERANGE.DATEFROM,
BOARDMEMBERDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
BOARDMEMBERDATERANGE.DATEADDED,
BOARDMEMBERDATERANGE.DATECHANGED,
BOARDMEMBERDATERANGE.TSLONG,
'F828E957-5F5E-479A-8F23-2FFD6C7C68FF' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.BOARDMEMBERDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = BOARDMEMBERDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = BOARDMEMBERDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Staff
select
STAFFDATERANGE.ID,
STAFFDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
STAFFDATERANGE.DATEFROM,
STAFFDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
STAFFDATERANGE.DATEADDED,
STAFFDATERANGE.DATECHANGED,
STAFFDATERANGE.TSLONG,
'6093915E-ADE9-42BE-88AE-304731754467' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.STAFFDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '6093915E-ADE9-42BE-88AE-304731754467') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = STAFFDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = STAFFDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Fundraiser
select
FUNDRAISERDATERANGE.ID,
FUNDRAISERDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
FUNDRAISERDATERANGE.DATEFROM,
FUNDRAISERDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
FUNDRAISERDATERANGE.DATEADDED,
FUNDRAISERDATERANGE.DATECHANGED,
FUNDRAISERDATERANGE.TSLONG,
'D2DCA06A-BE6E-40B3-B95D-59A926181923' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.FUNDRAISERDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = FUNDRAISERDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = FUNDRAISERDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Major giving prospect
select
PROSPECTDATERANGE.ID,
PROSPECTDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
PROSPECTDATERANGE.DATEFROM,
PROSPECTDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
PROSPECTDATERANGE.DATEADDED,
PROSPECTDATERANGE.DATECHANGED,
PROSPECTDATERANGE.TSLONG,
'00E748FB-940D-4A7D-A133-C148B29410A8' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.PROSPECTDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '00E748FB-940D-4A7D-A133-C148B29410A8') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = PROSPECTDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = PROSPECTDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Volunteer
select
VOLUNTEERDATERANGE.ID,
VOLUNTEERDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
VOLUNTEERDATERANGE.DATEFROM,
VOLUNTEERDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
VOLUNTEERDATERANGE.DATEADDED,
VOLUNTEERDATERANGE.DATECHANGED,
VOLUNTEERDATERANGE.TSLONG,
'E7489703-3D63-4017-A2BC-88C092563C5D' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.VOLUNTEERDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'E7489703-3D63-4017-A2BC-88C092563C5D') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = VOLUNTEERDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = VOLUNTEERDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Sponsor
select
SPONSORDATERANGE.ID,
SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
SPONSORDATERANGE.DATEFROM,
SPONSORDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
SPONSORDATERANGE.DATEADDED,
SPONSORDATERANGE.DATECHANGED,
SPONSORDATERANGE.TSLONG,
'4D746A03-A0AB-45F3-A30B-1AD4F304E622' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '4D746A03-A0AB-45F3-A30B-1AD4F304E622') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where SPONSORDATERANGE.SPONSORTYPECODE = 0 --Sponsor
union all
--Sponsorship recipient
select
SPONSORDATERANGE.ID,
SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
SPONSORDATERANGE.DATEFROM,
SPONSORDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
SPONSORDATERANGE.DATEADDED,
SPONSORDATERANGE.DATECHANGED,
SPONSORDATERANGE.TSLONG,
'F89E03BC-E724-4e5d-943B-72D4D1E1E916' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'F89E03BC-E724-4e5d-943B-72D4D1E1E916') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where SPONSORDATERANGE.SPONSORTYPECODE = 1 --Sponsorship recipient
union all
--Sponsorship donor
select
SPONSORDATERANGE.ID,
SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
SPONSORDATERANGE.DATEFROM,
SPONSORDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
SPONSORDATERANGE.DATEADDED,
SPONSORDATERANGE.DATECHANGED,
SPONSORDATERANGE.TSLONG,
'908E521C-B0A5-4832-B664-7D7B079D77C2' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '908E521C-B0A5-4832-B664-7D7B079D77C2') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORDATERANGE.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where SPONSORDATERANGE.SPONSORTYPECODE = 2 --Sponsorship donor
union all
--Bank
select
BANK.ID,
--14 as CONSTITUENTID,
BANK.ID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
convert(nchar(8), min(BANK.DATEADDED), 112),
null,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
BANK.DATEADDED,
BANK.DATECHANGED,
BANK.TSLONG,
'171AB3CD-C4E1-4825-B693-10F524A7A594' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.BANK
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '171AB3CD-C4E1-4825-B693-10F524A7A594') as CONSTITUENCYNAME
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = BANK.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = BANK.CHANGEDBYID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
group by
BANK.ID,
CONSTITUENCYNAME.DESCRIPTION,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
BANK.DATEADDED,
BANK.DATECHANGED,
BANK.TSLONG,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Donor (legacy based)
select
null, --System Record ID
DATA.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
min(DATA.REVENUEDATE), --Date from
case when INSTALLATIONINFO.DONORCONSTITUENCYDEFTYPECODE = 0 then
case when datediff(year, '17530101', getdate()) >= INSTALLATIONINFO.DONORCONSTITUENCYDEFLASTYEARS then
--The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...
case when max(DATA.REVENUEDATE) < dateadd(year, 0 - INSTALLATIONINFO.DONORCONSTITUENCYDEFLASTYEARS, dateadd(ms, -003, dateadd(d, 1, cast(cast(getdate() as date) as datetime)))) then
dateadd(year, INSTALLATIONINFO.DONORCONSTITUENCYDEFLASTYEARS, max(DATA.REVENUEDATE))
else
null
end
else
null
end
else
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
case when max(DATA.REVENUEDATE) < cast(INSTALLATIONINFO.DONORCONSTITUENCYDEFSTARTDATE as date) then
max(DATA.REVENUEDATE)
else
null
end
end, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'70165682-4324-46EC-9439-83FC0CC67E7F' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from (
select
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast([FT].[DATE] as date) as REVENUEDATE,
[FT].[CONSTITUENTID]
from
/* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
dbo.FINANCIALTRANSACTION as FT with (nolock)
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock) on FT.ID = FTLI.FINANCIALTRANSACTIONID
where FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER)
and FT.DELETEDON is null
union all
select
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast([FT].[DATE] as date) as REVENUEDATE,
REVENUERECOGNITION.CONSTITUENTID
from
/* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
dbo.FINANCIALTRANSACTION as FT with (nolock)
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock) on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join dbo.REVENUERECOGNITION on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
where (select top 1 CONSTITUENCYCRITERIASPOUSE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION from CONSTITUENCYCRITERIASPOUSE) = 1
and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER)
and FT.DELETEDON is null
) DATA
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '70165682-4324-46EC-9439-83FC0CC67E7F') as CONSTITUENCYNAME
left join dbo.INSTALLATIONINFO on 1 = 1
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
group by
DATA.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
INSTALLATIONINFO.DONORCONSTITUENCYDEFTYPECODE,
INSTALLATIONINFO.DONORCONSTITUENCYDEFLASTYEARS,
INSTALLATIONINFO.DONORCONSTITUENCYDEFSTARTDATE,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Vendor
select
null, --System Record ID
EVENTEXPENSE.VENDORID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
null, --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'5435C96D-8617-46C3-9A62-5AFF08451A53' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.EVENTEXPENSE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '5435C96D-8617-46C3-9A62-5AFF08451A53') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where EVENTEXPENSE.VENDORID is not null
group by
EVENTEXPENSE.VENDORID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Planned Giver
select
null, --System Record ID
PLANNEDGIFT.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast(min(PLANNEDGIFT.GIFTDATE) as date), --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.PLANNEDGIFT
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where (PLANNEDGIFT.STATUSCODE = 2 or PLANNEDGIFT.STATUSCODE = 5) --2=Accepted, 5=Matured
and exists (select ID
from dbo.PLANNEDGIFTDESIGNATION
where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID=PLANNEDGIFT.ID)
group by
PLANNEDGIFT.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--patron
select
null, --System Record ID
SALESORDER.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast(min(SALESORDER.TRANSACTIONDATE) as date), --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'A843B859-4C6B-445B-97F3-179582E270A5' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.SALESORDERITEM
--left join dbo.SALESORDERITEM on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
inner join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'A843B859-4C6B-445B-97F3-179582E270A5') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where SALESORDER.STATUSCODE <> 0 -- Orders that aren't Pending
and (
SALESORDER.SALESMETHODTYPECODE = 3 -- Group Sales
or SALESORDERITEM.TYPECODE in (0, 14) -- Ticket or Merchandise
)
group by
SALESORDER.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--recognized donor
select
null, --System Record ID
CONSTITUENTRECOGNITION.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast(MIN(CONSTITUENTRECOGNITION.JOINDATE) as date), --Date from
cast(MAX(CONSTITUENTRECOGNITION.EXPIRATIONDATE) as date), --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'3dfac92e-78bd-4051-abdc-02c675deb8f6' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.CONSTITUENTRECOGNITION
left join dbo.RECOGNITIONPROGRAM on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '3dfac92e-78bd-4051-abdc-02c675deb8f6') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where (CONSTITUENTRECOGNITION.EXPIRATIONDATE >= getdate() or RECOGNITIONPROGRAM.TYPECODE=1)
group by
CONSTITUENTRECOGNITION.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Member
select
null, --System Record ID
MEMBER.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast(MIN(MEMBERSHIP.JOINDATE) as date), --Date from
cast(MAX(MEMBERSHIP.EXPIRATIONDATE) as date), --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'2d11326e-8f3b-4322-9797-57c1aacfa5df' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.MEMBER
inner join dbo.MEMBERSHIP on MEMBER.MEMBERSHIPID=MEMBERSHIP.ID
inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '2d11326e-8f3b-4322-9797-57c1aacfa5df') as CONSTITUENCYNAME
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
where MEMBER.ISDROPPED=0
-- RENEWALAFTEREXPIRATIONDATE
and (getdate() <= dateadd(month, MEMBERSHIPLEVEL.AFTEREXPIRATION, MEMBERSHIP.EXPIRATIONDATE)
or MEMBERSHIPLEVELTERM.TERMCODE = 6)
and MEMBERSHIP.STATUSCODE <> 1
group by
MEMBER.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Matching gift org
select
null, --System Record ID
MATCHFINDERCONSTITUENT.ID , --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
MATCHFINDERCONSTITUENT.DATEADDED, --Date from
null, --Date to
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
MATCHFINDERCONSTITUENT.DATEADDED, --Date added
MATCHFINDERCONSTITUENT.DATECHANGED, --Date changed
MATCHFINDERCONSTITUENT.TS, --Timestamp value
'2D04A9C5-27D0-4646-BF0F-6826E4C12632' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.MATCHFINDERCONSTITUENT
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = MATCHFINDERCONSTITUENT.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = MATCHFINDERCONSTITUENT.CHANGEDBYID
where MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null
and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
union all
--Student
select
null, --System Record ID
EH.CONSTITUENTID, --Constituent ID
STUDENTCONSTITUENCYNAME.DESCRIPTION, --Constituency
cast(min(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.STARTDATE)) as date), --Date from
cast(max(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.ENDDATE)) as date), --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'4DB8F4FC-BC43-421D-B592-69BEF109B5FC' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.EDUCATIONALHISTORY EH
cross apply dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2(EH.ID) as EHSLINKING
inner join EDUCATIONALHISTORYSTATUS EHS on EHSLINKING.EDUCATIONALHISTORYSTATUSID = EHS.ID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC') as STUDENTCONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = STUDENTCONSTITUENCYNAME.ID
where EH.EDUCATIONALINSTITUTIONID in (select ID from dbo.SCHOOL)
and EHS.CONSTITUENCYIMPLIEDCODE = 0
group by
EH.CONSTITUENTID,
STUDENTCONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
-- Alumnus
select
null, --System Record ID
EH.CONSTITUENTID, --Constituent ID
ALUMNUSCONSTITUENCYNAME.DESCRIPTION, --Constituency
cast(min(dbo.UFN_DATE_EARLIESTFROMFUZZYDATE(EH.DATEGRADUATED)) as date), --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'46EC3424-BA54-4431-A7DC-C6CEBB3B4592' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.EDUCATIONALHISTORY EH
cross apply dbo.UFN_EDUCATIONALHISTORY_CURRENTEDUCATIONALHISTORYSTATUSINLINE2(EH.ID) as EHSLINKING
inner join EDUCATIONALHISTORYSTATUS EHS on EHSLINKING.EDUCATIONALHISTORYSTATUSID = EHS.ID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') as ALUMNUSCONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = ALUMNUSCONSTITUENCYNAME.ID
where EH.EDUCATIONALINSTITUTIONID in (select ID from dbo.SCHOOL)
and EHS.CONSTITUENCYIMPLIEDCODE = 1
group by
EH.CONSTITUENTID,
ALUMNUSCONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Committee
select
COMMITTEEDATERANGE.ID,
COMMITTEEDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
COMMITTEEDATERANGE.DATEFROM,
COMMITTEEDATERANGE.DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
COMMITTEEDATERANGE.DATEADDED,
COMMITTEEDATERANGE.DATECHANGED,
COMMITTEEDATERANGE.TSLONG,
'AC9DB5A4-14E0-416A-9FB2-04038AC66799' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.COMMITTEEDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = COMMITTEEDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = COMMITTEEDATERANGE.CHANGEDBYID
union all
--Grantor
select
GRANTOR.ID,
GRANTOR.ID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
convert(nchar(8), min(GRANTOR.DATEADDED), 112),
null,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
GRANTOR.DATEADDED,
GRANTOR.DATECHANGED,
GRANTOR.TSLONG,
'D9982C99-15C1-4C90-873E-56FD4B164056' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.GRANTOR
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'D9982C99-15C1-4C90-873E-56FD4B164056') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = GRANTOR.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = GRANTOR.CHANGEDBYID
group by
GRANTOR.ID,
CONSTITUENCYNAME.DESCRIPTION,
ADDEDBY.APPLICATIONNAME,
ADDEDBY.USERNAME,
CHANGEDBY.APPLICATIONNAME,
CHANGEDBY.USERNAME,
GRANTOR.DATEADDED,
GRANTOR.DATECHANGED,
GRANTOR.TSLONG,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Event registrant
select
null, --System Record ID
REGISTRANT.CONSTITUENTID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
--The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...
cast(min(REGISTRANT.DATEADDED) as date), --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'C49D4B46-72A7-4206-91AA-BEABA2323E3C' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.REGISTRANT
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
group by
REGISTRANT.CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Committee member
select
null, --System Record ID
GROUPMEMBER.MEMBERID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
min(GROUPMEMBERDATERANGE.DATEFROM), --Date from
case when min(isnull(convert(nchar(8),GROUPMEMBERDATERANGE.DATETO,112), '00000000')) = '00000000' then null else
max(GROUPMEMBERDATERANGE.DATETO)
end,
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'093A3D4F-2974-447F-AD92-870EB4A04593' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.GROUPMEMBER
inner join dbo.COMMITTEEDATERANGE on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
and (COMMITTEEDATERANGE.DATEFROM <= CONVERT(DATE,GETDATE()) or COMMITTEEDATERANGE.DATEFROM is null)
and (COMMITTEEDATERANGE.DATETO >= CONVERT(DATE,GETDATE()) or COMMITTEEDATERANGE.DATETO is null)
left join dbo.GROUPMEMBERDATERANGE on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '093A3D4F-2974-447F-AD92-870EB4A04593') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
group by
GROUPMEMBER.MEMBERID,
CONSTITUENCYNAME.DESCRIPTION,
CONSTITUENCYDEFINITION.ISACTIVE
union all
--Major donor
select
null, --System Record ID
MAJORDONORS.ID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
null, --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'1A9BFE80-604D-4B5B-8065-E751DDF6EF39' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.UFN_SELECTION_CONSTITUENT_MAJORDONORS () as MAJORDONORS
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Loyal donor
select
null, --System Record ID
LOYALDONORS.ID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
null, --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.UFN_SELECTION_CONSTITUENT_LOYALDONORS () as LOYALDONORS
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
union all
--Advocate
select
ADVOCATERDATERANGE.ID,
ADVOCATERDATERANGE.CONSTITUENTID as CONSTITUENTID,
CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY],
cast(ADVOCATERDATERANGE.DATEFROM as date) as DATEFROM,
cast(ADVOCATERDATERANGE.DATETO as date) as DATETO,
isnull([ADDEDBY].APPLICATIONNAME,'') as [ADDEDBY_APPLICATION],
isnull([ADDEDBY].USERNAME,'') as [ADDEDBY_USERNAME],
isnull([CHANGEDBY].APPLICATIONNAME,'') as [CHANGEDBY_APPLICATION],
isnull([CHANGEDBY].USERNAME,'') as [CHANGEDBY_USERNAME],
ADVOCATERDATERANGE.DATEADDED,
ADVOCATERDATERANGE.DATECHANGED,
ADVOCATERDATERANGE.TSLONG,
'F828E957-5F5E-479A-8F23-2FFD6C7C6899' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.ADVOCATERDATERANGE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C6899') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = ADVOCATERDATERANGE.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = ADVOCATERDATERANGE.CHANGEDBYID
union all
--Fundraising Group
select --distinct
null, --System Record ID
CONSTITUENT.ID, --Constituent ID
CONSTITUENCYNAME.DESCRIPTION, --Constituency
convert(nchar(8), TE.DATEADDED, 112), --Date from
null, --Date to
null, --Added by application
null, --Added by user name
null, --Changed by application
null, --Changed by user name
null, --Date added
null, --Date changed
null, --Timestamp value
'264818E9-3F59-44C1-9BBF-1BB1F9C78CA5' as [CONSTITUENCYDEFINITIONID],
CONSTITUENCYDEFINITION.ISACTIVE
from dbo.CONSTITUENT
cross apply
(
select top 1 TEAMEXTENSION.DATEADDED
from dbo.TEAMEXTENSION
where TEAMEXTENSION.TEAMCONSTITUENTID=CONSTITUENT.ID
order by TEAMEXTENSION.DATEADDED
) TE
outer apply (select top 1 CONSTITUENCYDEFINITION.ID, CONSTITUENCYDEFINITION.DESCRIPTION from dbo.CONSTITUENCYDEFINITION where CONSTITUENCYDEFINITION.ID = '264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') as CONSTITUENCYNAME
inner join dbo.CONSTITUENCYDEFINITION
on CONSTITUENCYDEFINITION.ID = CONSTITUENCYNAME.ID