UFN_CONSTITUENT_GETALLCONSTITUENCIES
Gets a list of constituencies for a constituent as of the given date.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(@ID uniqueidentifier)
returns @OUTPUT table(ID uniqueidentifier, CONSTITUENCYTYPECODE integer, DESCRIPTION nvarchar(100), DATEFROM dbo.UDT_FUZZYDATE, DATETO dbo.UDT_FUZZYDATE, CONSTITUENCYCODEID uniqueidentifier)
as
begin
--retrieve donor constituency information
declare @DONORCONSTITUENCYDEFTYPECODE int;
declare @DONORCONSTITUENCYDEFLASTYEARS int;
declare @DONORCONSTITUENCYDEFSTARTDATE datetime;
declare @CONSIDERRECOGNITION bit;
declare @DONORCONSTITUENCYSYSTEMNAME nvarchar(100);
select
@DONORCONSTITUENCYDEFTYPECODE = DONORCONSTITUENCYDEFTYPECODE,
@DONORCONSTITUENCYDEFLASTYEARS = DONORCONSTITUENCYDEFLASTYEARS,
@DONORCONSTITUENCYDEFSTARTDATE = DONORCONSTITUENCYDEFSTARTDATE
from
dbo.INSTALLATIONINFO;
select top 1
@CONSIDERRECOGNITION = MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION
from
dbo.CONSTITUENCYCRITERIASPOUSE;
select @DONORCONSTITUENCYSYSTEMNAME = DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where ID = '70165682-4324-46EC-9439-83FC0CC67E7F';
declare @CURRENTDATE date;
set @CURRENTDATE = getdate();
declare @DONORCONSTITUENCYLOWERDATEBOUND datetime;
if @DONORCONSTITUENCYDEFTYPECODE = 0
begin
declare @SQLMINDATE datetime = '17530101';
if datediff(year, @SQLMINDATE, dbo.UFN_DATE_GETLATESTTIME(getdate())) >= @DONORCONSTITUENCYDEFLASTYEARS
set @DONORCONSTITUENCYLOWERDATEBOUND = dateadd(year, 0 - @DONORCONSTITUENCYDEFLASTYEARS, dbo.UFN_DATE_GETLATESTTIME(@CURRENTDATE));
else
set @DONORCONSTITUENCYLOWERDATEBOUND = @SQLMINDATE;
end
else
set @DONORCONSTITUENCYLOWERDATEBOUND = dbo.UFN_DATE_GETEARLIESTTIME(@DONORCONSTITUENCYDEFSTARTDATE);
with CONSTITUENCIES_CTE as (
select
BOARDMEMBERDATERANGE.ID,
1 CONSTITUENCYTYPECODE,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),BOARDMEMBERDATERANGE.DATEFROM, 112) DATEFROM,
convert(char(8),BOARDMEMBERDATERANGE.DATETO, 112) DATETO,
CONSTITUENCYSYSTEMNAME.ID as CONSTITUENCYCODEID
from
dbo.BOARDMEMBERDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF'
where
BOARDMEMBERDATERANGE.CONSTITUENTID = @ID
union all
select
STAFFDATERANGE.ID,
2,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),STAFFDATERANGE.DATEFROM, 112),
convert(char(8),STAFFDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.STAFFDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '6093915E-ADE9-42BE-88AE-304731754467'
where
STAFFDATERANGE.CONSTITUENTID = @ID
union all
select
ADVOCATERDATERANGE.ID,
24 CONSTITUENCYTYPECODE,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),ADVOCATERDATERANGE.DATEFROM, 112),
convert(char(8),ADVOCATERDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.ADVOCATERDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C6899'
where
ADVOCATERDATERANGE.CONSTITUENTID = @ID
union all
select
FUNDRAISERDATERANGE.ID,
3,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),FUNDRAISERDATERANGE.DATEFROM, 112),
convert(char(8),FUNDRAISERDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.FUNDRAISERDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923'
where
FUNDRAISERDATERANGE.CONSTITUENTID = @ID
union all
select
PROSPECTDATERANGE.ID,
4,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),PROSPECTDATERANGE.DATEFROM, 112),
convert(char(8),PROSPECTDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.PROSPECTDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '00E748FB-940D-4A7D-A133-C148B29410A8'
where
PROSPECTDATERANGE.CONSTITUENTID = @ID
union all
select
VOLUNTEERDATERANGE.ID,
5,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),VOLUNTEERDATERANGE.DATEFROM, 112),
convert(char(8),VOLUNTEERDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.VOLUNTEERDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'E7489703-3D63-4017-A2BC-88C092563C5D'
where
VOLUNTEERDATERANGE.CONSTITUENTID = @ID
-- Student
union all
select
null,
6,
STUDENTNAME.DESCRIPTION,
min(EH.STARTDATE),
max(EH.ENDDATE),
STUDENTNAME.ID
from
dbo.EDUCATIONALHISTORY EH with (nolock)
inner join
dbo.EDUCATIONALINSTITUTION EI with (nolock) on EH.EDUCATIONALINSTITUTIONID = EI.ID and EI.ISAFFILIATED = 1
left join
dbo.CONSTITUENCYSYSTEMNAME STUDENTNAME with (nolock) on STUDENTNAME.ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC'
inner join
dbo.EDUCATIONALHISTORYSTATUS EHS on EH.EDUCATIONALHISTORYSTATUSID = EHS.ID
where
EH.CONSTITUENTID = @ID and
EHS.CONSTITUENCYIMPLIEDCODE = 0 and
((select top 1 ISCONSTITUENTWHENCODE from dbo.STUDENTCONSTITUENCYSETTINGS) = 0)
group by
STUDENTNAME.DESCRIPTION, STUDENTNAME.ID
-- Alumni
union all
select top 1
null,
6,
ALUMNUSNAME.DESCRIPTION,
min(EH.DATEGRADUATED),
null,
ALUMNUSNAME.ID
from
dbo.EDUCATIONALHISTORY EH with (nolock)
inner join
dbo.EDUCATIONALINSTITUTION EI with (nolock) on EH.EDUCATIONALINSTITUTIONID = EI.ID and EI.ISAFFILIATED = 1
left join
dbo.CONSTITUENCYSYSTEMNAME ALUMNUSNAME with (nolock) on ALUMNUSNAME.ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592'
inner join
dbo.EDUCATIONALHISTORYSTATUS EHS on EH.EDUCATIONALHISTORYSTATUSID = EHS.ID
where
EH.CONSTITUENTID = @ID and
EHS.CONSTITUENCYIMPLIEDCODE = 1
group by ALUMNUSNAME.DESCRIPTION, ALUMNUSNAME.ID
union all
-- no longer return 'Community member' as a constituency; now just surfaced through 'Online information' page
--select
-- null,
-- 7,
-- CONSTITUENCYSYSTEMNAME.DESCRIPTION,
-- convert(char(8), dbo.UFN_DATE_GETEARLIESTTIME(coalesce(NETCOMMUNITYCLIENTUSER.USERADDEDDATE, NETCOMMUNITYCLIENTUSER.SIGNUPTRANSACTIONDATE)), 112),
-- null,
-- CONSTITUENCYSYSTEMNAME.ID
--from
-- dbo.CONSTITUENT with (nolock)
--left join
-- dbo.NETCOMMUNITYCLIENTUSER with (nolock) on CONSTITUENT.ID = NETCOMMUNITYCLIENTUSER.CONSTITUENTID
--left join
-- dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '55FE8E7C-2B68-44C8-B35C-818AD1944C03'
--where
-- CONSTITUENT.ID = @ID
-- and NETCOMMUNITYCLIENTUSER.ACTIVE = 1
-- and NETCOMMUNITYCLIENTUSER.DELETED = 0
--union all
select top 1
null,
8,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),min(REGISTRANT.DATEADDED),112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.REGISTRANT with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C'
where
REGISTRANT.CONSTITUENTID = @ID
group by
REGISTRANT.CONSTITUENTID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
--Donor
select
null,
10 as CONSTITUENCYTYPECODE,
@DONORCONSTITUENCYSYSTEMNAME,
convert(char(8),min(DATA.REVENUEDATE),112),
case
when max(DATA.REVENUEDATE) < @DONORCONSTITUENCYLOWERDATEBOUND then
case
when @DONORCONSTITUENCYDEFTYPECODE = 0 then
convert(char(8), dateadd(year, @DONORCONSTITUENCYDEFLASTYEARS, max(DATA.REVENUEDATE)), 112)
else
convert(char(8), max(DATA.REVENUEDATE), 112)
end
else null
end as DATETO,
'70165682-4324-46EC-9439-83FC0CC67E7F'
from(
select
[FT].[DATE] as REVENUEDATE
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
FT.CONSTITUENTID = @ID
and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER)
and FT.DELETEDON is null
union all
select
[FT].[DATE] as REVENUEDATE
from
/* JamesWill WI122678 Make the donor constituency match up with UFN_CONSTITUENT_ISDONOR by using UFN_REVENUELIFECYCLE_GETVALIDREVENUE*/
dbo.REVENUERECOGNITION
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI with (nolock) on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.FINANCIALTRANSACTION as FT with (nolock) on FT.ID = FTLI.FINANCIALTRANSACTIONID
where
REVENUERECOGNITION.CONSTITUENTID = @ID
and FTLI.ID in (select ID from dbo.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER)
and @CONSIDERRECOGNITION = 1
and FT.DELETEDON is null
) DATA
having
min(DATA.REVENUEDATE) is not null
union all
select
null,
11,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),min(RELATIONSHIP.DATEADDED),112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.RELATIONSHIP
left join
dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCYSYSTEMNAME.ID = '8B6ECCFD-6829-4FBC-B092-58CA083F9322'
where
RELATIONSHIP.RELATIONSHIPCONSTITUENTID = @ID
group by
RELATIONSHIP.RELATIONSHIPCONSTITUENTID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
select
null,
12,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),SPONSORDATERANGE.DATEFROM, 112),
convert(char(8),SPONSORDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.SPONSORDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID =
case SPONSORDATERANGE.SPONSORTYPECODE
when 0 then '4D746A03-A0AB-45F3-A30B-1AD4F304E622'
when 1 then 'F89E03BC-E724-4e5d-943B-72D4D1E1E916'
when 2 then '908E521C-B0A5-4832-B664-7D7B079D77C2'
end
where
SPONSORDATERANGE.CONSTITUENTID = @ID
union all
select
CONSTITUENCY.ID,
99,
CODE.DESCRIPTION,
convert(char(8),CONSTITUENCY.DATEFROM, 112),
convert(char(8),CONSTITUENCY.DATETO, 112),
CODE.ID
from
dbo.CONSTITUENCY with (nolock)
left join
dbo.CONSTITUENCYCODE CODE with (nolock)
on
CODE.ID = CONSTITUENCY.CONSTITUENCYCODEID
where
CONSTITUENCY.CONSTITUENTID = @ID
union all
select
null,
13,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8), min(MATCHFINDERCONSTITUENT.DATEADDED), 112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.MATCHFINDERCONSTITUENT with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632'
where
MATCHFINDERCONSTITUENT.ID = @ID
group by
MATCHFINDERCONSTITUENT.ID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
select
null,
14,
'Planned giver',
convert(char(8),min(PLANNEDGIFT.GIFTDATE),112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.PLANNEDGIFT with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634'
where
PLANNEDGIFT.CONSTITUENTID = @ID
and dbo.UFN_CONSTITUENT_ISPLANNEDGIVER(PLANNEDGIFT.CONSTITUENTID) = 1
group by
PLANNEDGIFT.CONSTITUENTID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
select
null,
15,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8), (dbo.UFN_CONSTITUENT_GETPATRONSTARTDATE(@ID)),112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
CONSTITUENT with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'A843B859-4C6B-445B-97F3-179582E270A5'
where
CONSTITUENT.ID = @ID
and
dbo.UFN_CONSTITUENT_ISPATRON(@ID) = 1
union all
select
COMMITTEEDATERANGE.ID,
16,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8),COMMITTEEDATERANGE.DATEFROM, 112),
convert(char(8),COMMITTEEDATERANGE.DATETO, 112),
CONSTITUENCYSYSTEMNAME.ID
from
dbo.COMMITTEEDATERANGE with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799'
where
COMMITTEEDATERANGE.CONSTITUENTID = @ID
union all
select top(1)
null,
17,
CSN.DESCRIPTION,
convert(char(8),dbo.UFN_CONSTITUENT_GETEARLIESTMEMBERSHIPJOINDATE(@ID), 112),
convert(char(8),dbo.UFN_CONSTITUENT_GETLATESTMEMBERSHIPEXPIRATIONDATE(@ID), 112),
CSN.ID
from
dbo.MEMBER M with (nolock)
inner join dbo.MEMBERSHIP with (nolock) on MEMBERSHIP.[ID] = M.[MEMBERSHIPID]
left outer join
dbo.CONSTITUENCYSYSTEMNAME CSN with (nolock) on CSN.ID = '2D11326E-8F3B-4322-9797-57C1AACFA5DF'
where
M.CONSTITUENTID = @ID and M.[ISDROPPED] = 0 and MEMBERSHIP.[STATUSCODE] = 0
union all
select top 1
GRANTOR.ID,
20,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8), GRANTOR.DATEADDED, 112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.GRANTOR with (nolock)
left join dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = 'D9982C99-15C1-4C90-873E-56FD4B164056'
where
GRANTOR.ID = @ID
union all
select
null,
16,
CONSTITUENCYSYSTEMNAME.DESCRIPTION, --Constituency
convert(char(8),MIN(CONSTITUENTRECOGNITION.JOINDATE),112), --Date from
convert(char(8),MAX(CONSTITUENTRECOGNITION.EXPIRATIONDATE),112), --Date to
CONSTITUENCYSYSTEMNAME.ID
from
dbo.CONSTITUENTRECOGNITION with (nolock)
left join dbo.RECOGNITIONPROGRAM with (nolock) on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
left join dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '3dfac92e-78bd-4051-abdc-02c675deb8f6'
where
CONSTITUENTRECOGNITION.CONSTITUENTID = @ID and
(CONSTITUENTRECOGNITION.EXPIRATIONDATE >= getdate() or RECOGNITIONPROGRAM.TYPECODE=1)
group by CONSTITUENTRECOGNITION.CONSTITUENTID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
select
null, --System Record ID
18,
CONSTITUENCYSYSTEMNAME.DESCRIPTION, --Constituency
min(coalesce(convert(char(8),GROUPMEMBERDATERANGE.DATEFROM,112), '00000000')),
--If there is a blank date return that
case when min(coalesce(convert(char(8),GROUPMEMBERDATERANGE.DATETO,112), '00000000')) = '00000000' then '00000000' else
max(coalesce(convert(char(8),GROUPMEMBERDATERANGE.DATETO,112), '00000000'))
end,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.GROUPMEMBER with (nolock)
inner join dbo.COMMITTEEDATERANGE on COMMITTEEDATERANGE.CONSTITUENTID = GROUPMEMBER.GROUPID
left join dbo.GROUPMEMBERDATERANGE with (nolock) on GROUPMEMBER.ID = GROUPMEMBERDATERANGE.GROUPMEMBERID
left join dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '093A3D4F-2974-447F-AD92-870EB4A04593'
where
GROUPMEMBER.MEMBERID = @ID and
(COMMITTEEDATERANGE.DATEFROM <= @CURRENTDATE or COMMITTEEDATERANGE.DATEFROM is null) and
(COMMITTEEDATERANGE.DATETO >= @CURRENTDATE or COMMITTEEDATERANGE.DATETO is null)
group by
CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
select
BANK.ID,
19,
CONSTITUENCYSYSTEMNAME.DESCRIPTION,
convert(char(8), min(BANK.DATEADDED), 112),
null,
CONSTITUENCYSYSTEMNAME.ID
from
dbo.BANK with (nolock)
left join
dbo.CONSTITUENCYSYSTEMNAME with (nolock) on CONSTITUENCYSYSTEMNAME.ID = '171AB3CD-C4E1-4825-B693-10F524A7A594'
where
BANK.ID = @ID
group by
BANK.ID, CONSTITUENCYSYSTEMNAME.DESCRIPTION, CONSTITUENCYSYSTEMNAME.ID
union all
--Major donor
select
null, --System Record ID
21,
CONSTITUENCYSYSTEMNAME.DESCRIPTION, --Constituency
null, --Date from
null, --Date to
CONSTITUENCYSYSTEMNAME.ID
from
dbo.CONSTITUENT
left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCYSYSTEMNAME.ID = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39'
where
CONSTITUENT.ID = @ID
and dbo.UFN_CONSTITUENT_ISMAJORDONOR(@ID) = 1
union all
--Loyal donor
select
null, --System Record ID
22,
CONSTITUENCYSYSTEMNAME.DESCRIPTION, --Constituency
null, --Date from
null, --Date to
CONSTITUENCYSYSTEMNAME.ID
from
dbo.CONSTITUENT
left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCYSYSTEMNAME.ID = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B'
where
CONSTITUENT.ID = @ID
and dbo.UFN_CONSTITUENT_ISLOYALDONOR(@ID) = 1
union all
--Student relation constituencies based on relationship constituency criteria
select distinct
null, --System Record ID
23,
RELATIONCONSTITUENCIES.CONSTITUENCYCODEDESCRIPTION, --Constituency
min(EDUCATIONALHISTORY.STARTDATE), --Date from
null, --Date to
RELATIONCONSTITUENCIES.CONSTITUENCYCODEID
from
dbo.UFN_CONSTITUENT_STUDENTRELATIONCONSTITUENCIES() as RELATIONCONSTITUENCIES
inner join dbo.EDUCATIONALHISTORY on RELATIONCONSTITUENCIES.RECIPROCALCONSTITUENTID = EDUCATIONALHISTORY.CONSTITUENTID
where
RELATIONCONSTITUENCIES.CONSTITUENTID = @ID
and RELATIONCONSTITUENCIES.ISCONSTITUENT = 1
group by RELATIONCONSTITUENCIES.CONSTITUENCYCODEDESCRIPTION, RELATIONCONSTITUENCIES.CONSTITUENCYCODEID
union all
--Fundraising group
select
null, --System Record ID
24,
CONSTITUENCYSYSTEMNAME.DESCRIPTION, --Constituency
convert(char(8), TE.DATEADDED, 112), --Date from
null, --Date to
CONSTITUENCYSYSTEMNAME.ID
from
dbo.CONSTITUENT
left join dbo.CONSTITUENCYSYSTEMNAME on CONSTITUENCYSYSTEMNAME.ID = '264818E9-3F59-44C1-9BBF-1BB1F9C78CA5'
cross apply
(
select top 1 TEAMEXTENSION.DATEADDED
from dbo.TEAMEXTENSION
where TEAMEXTENSION.TEAMCONSTITUENTID=CONSTITUENT.ID
order by TEAMEXTENSION.DATEADDED
) TE
where
CONSTITUENT.ID = @ID
and dbo.UFN_CONSTITUENT_ISFUNDRAISINGGROUP(@ID) = 1
)
insert into @OUTPUT
select
C.ID,
C.CONSTITUENCYTYPECODE,
C.DESCRIPTION,
coalesce(C.DATEFROM, '00000000'),
coalesce(C.DATETO, '00000000'),
C.CONSTITUENCYCODEID
from
CONSTITUENCIES_CTE C with (nolock)
where
C.CONSTITUENCYTYPECODE <> 11 or
(select count(C.CONSTITUENCYTYPECODE) from CONSTITUENCIES_CTE C) = 1
return;
end