V_QUERY_SPONSOR

/*
Generated by Blackbaud AppFx Platform
Date:  3/28/2016 5:45:31 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.158.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSOR AS



-- removed the SPONSORS CTE as it wasn't needed

-- also removed the currency fields from the GROUP BY in the cte below and ensured they are properly calculated in the select statement. When multicurrency was added to this view

-- the one result per sponsor desired behavior was broken, because different exchange rates/base currencies would cause multiple result below, therefore we had to put conditional

-- case logic in the select statements to prevent that

-- This queryview is intended to produce one result per SPONSOR!!!

with SPONSORSHIPPAYMENTS as
(
  select
    FINANCIALTRANSACTION.CONSTITUENTID,
    case
      when min(cast(isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as nvarchar(36))) = max(cast(isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as nvarchar(36))) then sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT)
      else null
    end as SPONSORSHIPTOTALAMOUNT,
    case
      when min(cast(isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as nvarchar(36))) = max(cast(isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as nvarchar(36))) then cast(min(cast(isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as nvarchar(36))) as uniqueidentifier)
      else null
    end as BASECURRENCYID,
    sum(FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT) as SPONSORSHIPTOTALORGANIZATIONAMOUNT,
    case
      when min(cast(FINANCIALTRANSACTION.ORGEXCHANGERATEID as nvarchar(36))) = max(cast(FINANCIALTRANSACTION.ORGEXCHANGERATEID as nvarchar(36))) then cast(min(cast(FINANCIALTRANSACTION.ORGEXCHANGERATEID as nvarchar(36))) as uniqueidentifier)
      else null
    end as ORGANIZATIONEXCHANGERATEID
  from dbo.FINANCIALTRANSACTIONLINEITEM
    inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
    inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
    inner join dbo.REVENUE_EXT on REVENUE_EXT.ID = FINANCIALTRANSACTION.ID
    inner join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = V.FINANCIALTRANSACTIONID
    inner join SPONSOR on SPONSOR.ID = FINANCIALTRANSACTION.CONSTITUENTID
  where
    REVENUESPLIT_EXT.TYPECODE = 9 and
    FINANCIALTRANSACTION.TYPECODE = 0 and
    FINANCIALTRANSACTION.DELETEDON is null
  group by FINANCIALTRANSACTION.CONSTITUENTID
)
select
  SPONSOR.ID,
  SPONSOR.DONOTTERMINATE,
  (
    select count(1)
    from dbo.SPONSORSHIP
    where
      CONSTITUENTID = SPONSOR.ID and
      STATUSCODE = 1
  ) as ACTIVESPONSORSHIPS,
  SPONSOR.UNIQUEOPPORTUNITIESFORGIFTDONOR,
  (
    select min(DATEFROM)
    from dbo.SPONSORDATERANGE
    where CONSTITUENTID = SPONSOR.ID
  ) as [SPONSORSINCE],
  SPONSORSHIPPAYMENTS.SPONSORSHIPTOTALAMOUNT,
  case 
    (
      select count(1)
      from dbo.SPONSORSHIP
      where
        CONSTITUENTID = SPONSOR.ID
        and STATUSCODE = 1
    )
    when 0 then 'Inactive'
    else 'Active'
  end as [STATUS],
  [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
  [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
  [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
  [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
  SPONSOR.DATEADDED,
  SPONSOR.DATECHANGED,
  SPONSOR.TSLONG,
  (
    select count(1)
    from dbo.SPONSORSHIP
      inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = SPONSORSHIP.REVENUESPLITID
      inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
    where
      FINANCIALTRANSACTION.CONSTITUENTID = SPONSOR.ID and
      FINANCIALTRANSACTION.DELETEDON is null and
      SPONSORSHIP.STATUSCODE = 1
  ) as ACTIVECOMMITMENTS,
  SPONSORSHIPPAYMENTS.BASECURRENCYID,
  SPONSORSHIPPAYMENTS.SPONSORSHIPTOTALORGANIZATIONAMOUNT,
  SPONSORSHIPPAYMENTS.ORGANIZATIONEXCHANGERATEID
from dbo.SPONSOR
  left join SPONSORSHIPPAYMENTS on SPONSORSHIPPAYMENTS.CONSTITUENTID = SPONSOR.ID
  left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSOR.ADDEDBYID
  left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSOR.CHANGEDBYID