V_QUERY_RESEARCHGROUPADDMEMBERS_CONSTITUENCY

/*
Generated by Blackbaud AppFx Platform
Date:  11/11/2014 4:10:12 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.2.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_RESEARCHGROUPADDMEMBERS_CONSTITUENCY AS



select
  CONSTITUENCY.ID,
  CONSTITUENCY.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYCODE.DESCRIPTION [CONSTITUENCY]
from dbo.CONSTITUENCY
left join dbo.CONSTITUENCYCODE on CONSTITUENCYCODE.ID = CONSTITUENCY.CONSTITUENCYCODEID

union all

--Board member

select
  BOARDMEMBERDATERANGE.ID,
  BOARDMEMBERDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.BOARDMEMBERDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C68FF') as CONSTITUENCYNAME

union all

--Staff

select
  STAFFDATERANGE.ID,
  STAFFDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.STAFFDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '6093915E-ADE9-42BE-88AE-304731754467') as CONSTITUENCYNAME

union all

--Fundraiser

select
  FUNDRAISERDATERANGE.ID,
  FUNDRAISERDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.FUNDRAISERDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'D2DCA06A-BE6E-40B3-B95D-59A926181923') as CONSTITUENCYNAME

union all

--Major giving prospect

select
  PROSPECTDATERANGE.ID,
  PROSPECTDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.PROSPECTDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '00E748FB-940D-4A7D-A133-C148B29410A8') as CONSTITUENCYNAME

union all

--Volunteer

select
  VOLUNTEERDATERANGE.ID,
  VOLUNTEERDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.VOLUNTEERDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'E7489703-3D63-4017-A2BC-88C092563C5D') as CONSTITUENCYNAME

union all

--Sponsor

select
  SPONSORDATERANGE.ID,
  SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '4D746A03-A0AB-45F3-A30B-1AD4F304E622') as CONSTITUENCYNAME
where SPONSORDATERANGE.SPONSORTYPECODE = 0  --Sponsor


union all

--Sponsorship recipient

select
  SPONSORDATERANGE.ID,
  SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'F89E03BC-E724-4e5d-943B-72D4D1E1E916') as CONSTITUENCYNAME
where SPONSORDATERANGE.SPONSORTYPECODE = 1  --Sponsorship recipient


union all

--Sponsorship donor

select
  SPONSORDATERANGE.ID,
  SPONSORDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.SPONSORDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '908E521C-B0A5-4832-B664-7D7B079D77C2') as CONSTITUENCYNAME
where SPONSORDATERANGE.SPONSORTYPECODE = 2  --Sponsorship donor


union all

--Bank

select
  BANK.ID,
  --14 as CONSTITUENTID,

  BANK.ID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.BANK
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '171AB3CD-C4E1-4825-B693-10F524A7A594') as CONSTITUENCYNAME
group by BANK.ID, CONSTITUENCYNAME.DESCRIPTION

union all

--Donor (legacy based)

select
  null, --System Record ID

  DATA.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
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.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on REVENUEFILTER.ID = FTLI.ID
  inner join dbo.FINANCIALTRANSACTION as FT  with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID

  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.UFN_REVENUEFILTER_DEFAULT() as REVENUEFILTER
  inner join dbo.FINANCIALTRANSACTIONLINEITEM as FTLI  with (nolock) on REVENUEFILTER.ID = FTLI.ID
  inner join dbo.FINANCIALTRANSACTION as FT  with (nolock) on FTLI.FINANCIALTRANSACTIONID = FT.ID
  inner join dbo.REVENUERECOGNITION on FTLI.ID = REVENUERECOGNITION.REVENUESPLITID
  outer apply dbo.CONSTITUENCYCRITERIASPOUSE
  where CONSTITUENCYCRITERIASPOUSE.MAJORDONORCONSTITUENCYDEFCONSIDERRECOGNITION = 1 
) DATA
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '70165682-4324-46EC-9439-83FC0CC67E7F') as CONSTITUENCYNAME
left join dbo.INSTALLATIONINFO on 1 = 1
group by
  DATA.CONSTITUENTID, CONSTITUENCYNAME.DESCRIPTION, INSTALLATIONINFO.DONORCONSTITUENCYDEFTYPECODE, INSTALLATIONINFO.DONORCONSTITUENCYDEFLASTYEARS,
  INSTALLATIONINFO.DONORCONSTITUENCYDEFSTARTDATE

union all

--Vendor

select
  null, --System Record ID

  EVENTEXPENSE.VENDORID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.EVENTEXPENSE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '5435C96D-8617-46C3-9A62-5AFF08451A53') as CONSTITUENCYNAME
where EVENTEXPENSE.VENDORID is not null
group by EVENTEXPENSE.VENDORID, CONSTITUENCYNAME.DESCRIPTION

union all

--Planned Giver

select
  null, --System Record ID

  PLANNEDGIFT.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.PLANNEDGIFT
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'CEE46FE7-3FBB-4DFE-97EB-BA67DD33C634') as CONSTITUENCYNAME
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

union all

--patron

select
  null, --System Record ID

  SALESORDER.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.SALESORDERITEM 
left join dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'A843B859-4C6B-445B-97F3-179582E270A5') as CONSTITUENCYNAME
where SALESORDER.STATUSCODE <> 0
group by SALESORDER.CONSTITUENTID, CONSTITUENCYNAME.DESCRIPTION

union all

--recognized donor

select
  null, --System Record ID

  CONSTITUENTRECOGNITION.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.CONSTITUENTRECOGNITION
left join dbo.RECOGNITIONPROGRAM on CONSTITUENTRECOGNITION.RECOGNITIONPROGRAMID = RECOGNITIONPROGRAM.ID
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '3dfac92e-78bd-4051-abdc-02c675deb8f6') as CONSTITUENCYNAME
where (CONSTITUENTRECOGNITION.EXPIRATIONDATE >= getdate() or RECOGNITIONPROGRAM.TYPECODE=1)
group by CONSTITUENTRECOGNITION.CONSTITUENTID, CONSTITUENCYNAME.DESCRIPTION

union all

--Member

select
  null, --System Record ID

  MEMBER.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
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 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '2d11326e-8f3b-4322-9797-57c1aacfa5df') as CONSTITUENCYNAME
left join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.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

union all

--Matching gift org

select
  null, --System Record ID

  MATCHFINDERCONSTITUENT.ID , --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.MATCHFINDERCONSTITUENT 
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from  dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '2D04A9C5-27D0-4646-BF0F-6826E4C12632') as CONSTITUENCYNAME
where MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID is not null 
and MATCHFINDERCONSTITUENT.MATCHFINDERRECORDID <> 0
group by MATCHFINDERCONSTITUENT.ID, CONSTITUENCYNAME.DESCRIPTION

union all

--Student

select
  null, --System Record ID

  EH.CONSTITUENTID, --Constituent ID

  STUDENTCONSTITUENCYNAME.DESCRIPTION
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 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '4DB8F4FC-BC43-421D-B592-69BEF109B5FC') as STUDENTCONSTITUENCYNAME
where EH.EDUCATIONALINSTITUTIONID in (select ID from dbo.SCHOOL)
group by EH.CONSTITUENTID, STUDENTCONSTITUENCYNAME.DESCRIPTION
-- Only look at the Student and Alumni CONSTITUENCYIMPLIEDCODE values

having max(case when EHS.CONSTITUENCYIMPLIEDCODE in (0, 1) then EHS.CONSTITUENCYIMPLIEDCODE else null end) = 0

union all

-- Alumnus

select
  null, --System Record ID

  EH.CONSTITUENTID, --Constituent ID

  ALUMNUSCONSTITUENCYNAME.DESCRIPTION
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 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '46EC3424-BA54-4431-A7DC-C6CEBB3B4592') as ALUMNUSCONSTITUENCYNAME
where EH.EDUCATIONALINSTITUTIONID in (select ID from dbo.SCHOOL)
group by EH.CONSTITUENTID, ALUMNUSCONSTITUENCYNAME.DESCRIPTION
-- Only look at the Student and Alumni CONSTITUENCYIMPLIEDCODE values

having max(case when EHS.CONSTITUENCYIMPLIEDCODE in (0, 1) then EHS.CONSTITUENCYIMPLIEDCODE else null end) = 1

union all

--Committee

select
  COMMITTEEDATERANGE.ID,
  COMMITTEEDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.COMMITTEEDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'AC9DB5A4-14E0-416A-9FB2-04038AC66799') as CONSTITUENCYNAME

union all

--Grantor

select
  GRANTOR.ID,
  GRANTOR.ID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.GRANTOR
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'D9982C99-15C1-4C90-873E-56FD4B164056') as CONSTITUENCYNAME
group by GRANTOR.ID, CONSTITUENCYNAME.DESCRIPTION

union all

--Event registrant

select
  null, --System Record ID

  REGISTRANT.CONSTITUENTID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.REGISTRANT
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'C49D4B46-72A7-4206-91AA-BEABA2323E3C') as CONSTITUENCYNAME
group by REGISTRANT.CONSTITUENTID, CONSTITUENCYNAME.DESCRIPTION

union all

--Committee member

select
  null, --System Record ID

  GROUPMEMBER.MEMBERID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
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 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '093A3D4F-2974-447F-AD92-870EB4A04593') as CONSTITUENCYNAME
group by
  GROUPMEMBER.MEMBERID,
  CONSTITUENCYNAME.DESCRIPTION

union all

--Major donor

select
  null, --System Record ID

  MAJORDONORS.ID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.UFN_SELECTION_CONSTITUENT_MAJORDONORS () as MAJORDONORS
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '1A9BFE80-604D-4B5B-8065-E751DDF6EF39') as CONSTITUENCYNAME

union all

--Loyal donor

select
  null, --System Record ID

  LOYALDONORS.ID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
from dbo.UFN_SELECTION_CONSTITUENT_LOYALDONORS () as LOYALDONORS
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'E5A0EA42-65BA-4B25-AFE2-9B709F99E72B') as CONSTITUENCYNAME

union all

--Advocate

select
  ADVOCATERDATERANGE.ID,
  ADVOCATERDATERANGE.CONSTITUENTID as CONSTITUENTID,
  CONSTITUENCYNAME.DESCRIPTION [CONSTITUENCY]
from dbo.ADVOCATERDATERANGE
outer apply (select top 1 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = 'F828E957-5F5E-479A-8F23-2FFD6C7C6899') as CONSTITUENCYNAME

union all

--Fundraising Group

select --distinct

  null, --System Record ID

  CONSTITUENT.ID, --Constituent ID

  CONSTITUENCYNAME.DESCRIPTION
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 CONSTITUENCYSYSTEMNAME.DESCRIPTION from dbo.CONSTITUENCYSYSTEMNAME where CONSTITUENCYSYSTEMNAME.ID = '264818E9-3F59-44C1-9BBF-1BB1F9C78CA5') as CONSTITUENCYNAME