UFN_APPEAL_REVENUECOUNTS_2

create function dbo.[UFN_APPEAL_REVENUECOUNTS_2]
(
  @STARTDATE datetime, -- expects EARLIESTTIME

  @ENDDATE datetime    -- expects LATESTTIME

)
returns table
as
return
(
  -- the CTE is necessary because selecting and aggregating at the same time may distort DONORCOUNT

  -- (a person may give multiple types of gifts)

  with [APPEALREVENUE] as
  (
    select distinct
      [BASEREVENUE].[APPEALID],
      [BASEREVENUE].[ID] as [REVENUEID],
      [FT].[CONSTITUENTID],
      case when ([FT].[TYPECODE] = 0 and [REVENUESPLIT].[APPLICATIONCODE] = 3) then [BASEREVENUE].[ID] else null end as [REGULARREVENUEID]
    from dbo.[REVENUE_EXT] as [BASEREVENUE]
    inner join dbo.[FINANCIALTRANSACTION] as [FT] on [FT].[ID] = [BASEREVENUE].[ID]
    inner join dbo.[FINANCIALTRANSACTIONLINEITEM] as [LI] on [LI].[FINANCIALTRANSACTIONID] = [FT].[ID]
    inner join dbo.[REVENUESPLIT_EXT] as [REVENUESPLIT] on [LI].[ID] = [REVENUESPLIT].[ID]
    left outer join dbo.[DESIGNATION] on [REVENUESPLIT].[DESIGNATIONID] = [DESIGNATION].[ID]
    where [BASEREVENUE].[APPEALID] is not null
    and (cast([FT].[DATE] as datetime) >= @STARTDATE or @STARTDATE is null)
    and (cast([FT].[DATE] as datetime) <= @ENDDATE or @ENDDATE is null)
    and [FT].[TYPECODE] in (0,1,2,3,4,5,6,7,8,9)
    and [LI].[DELETEDON] is null and [LI].[TYPECODE] <> 1
  )
  select
    [APPEALREVENUE].[APPEALID],
    count(distinct [CONSTITUENTID]) as [DONORCOUNT],
    count(distinct [REVENUEID]) as [GIFTCOUNT],
    count(distinct [REGULARREVENUEID]) as [REGULARGIFTCOUNT],
    count(distinct [REVENUEID]) - count(distinct [REGULARREVENUEID]) as [CASHGIFTCOUNT]
  from [APPEALREVENUE]
  group by [APPEALREVENUE].[APPEALID]
);