UFN_CONSTITUENT_GETMATCHEDTOTALFORYEARINCURRENCY

Returns the total amount matched for a constituent by an organization in a specific year for revenue in a specific currency.

Return

Return Type
money

Parameters

Parameter	Parameter Type	Mode
@CONSTITUENTID	uniqueidentifier	IN
@MATCHINGORGANIZATIONID	uniqueidentifier	IN
@YEARSTART	datetime	IN
@TRANSACTIONCURRENCYID	uniqueidentifier	IN

Definition

Copy



CREATE function dbo.UFN_CONSTITUENT_GETMATCHEDTOTALFORYEARINCURRENCY
(
  @CONSTITUENTID uniqueidentifier = null,
  @MATCHINGORGANIZATIONID uniqueidentifier = null,
  @YEARSTART datetime,
  @TRANSACTIONCURRENCYID uniqueidentifier = null
)
returns money
as
begin
  declare @r money;

  declare @YEAREND datetime = dateadd(month, 12, @YEARSTART);

  if @TRANSACTIONCURRENCYID is null
    set @TRANSACTIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

  declare @CONSTITUENT_GIFT table
  (
    ID uniqueidentifier
  );

  insert into
    @CONSTITUENT_GIFT
  select
    ID
  from
    dbo.FINANCIALTRANSACTION
  where
    CONSTITUENTID = @CONSTITUENTID
    and
    DELETEDON is null
    and
    [DATE] between @YEARSTART and dbo.UFN_DATE_GETLATESTTIME(@YEAREND);

  select
    @r = coalesce(sum(MGREVENUE.TRANSACTIONAMOUNT), 0)
  from
    @CONSTITUENT_GIFT as ORIGINALREVENUE
  inner join
    dbo.REVENUEMATCHINGGIFT as RMG on RMG.MGSOURCEREVENUEID = ORIGINALREVENUE.ID
  inner join
    dbo.FINANCIALTRANSACTION as MGREVENUE on MGREVENUE.ID = RMG.ID
  where
    MGREVENUE.CONSTITUENTID = @MATCHINGORGANIZATIONID
    and
    MGREVENUE.TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID
    and
    MGREVENUE.DELETEDON is null;

  return @r;
end