UFN_APPEAL_REVENUECOUNTS

CREATE function dbo.UFN_APPEAL_REVENUECOUNTS
(
    @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 REVENUEID,
            FT.CONSTITUENTID,
            case when (FT.TYPECODE = 0 and REVENUESPLIT.APPLICATIONCODE = 3) then 1 else 0 end ISREGULARGIFT
        from dbo.REVENUE_EXT BASEREVENUE
            inner join dbo.FINANCIALTRANSACTION FT on FT.ID = BASEREVENUE.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.FINANCIALTRANSACTIONID = FT.ID
            inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on LI.ID = REVENUESPLIT.ID
            left join dbo.DESIGNATION d on REVENUESPLIT.DESIGNATIONID = d.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 = 0 and REVENUESPLIT.APPLICATIONCODE = 3)
                or
                (FT.TYPECODE in (1,3,4,6,7,8) or REVENUESPLIT.APPLICATIONCODE in (0,1,4))
            )
            and LI.DELETEDON is null and LI.TYPECODE != 1
    )
    select
        APPEALREVENUE.APPEALID,
        count(distinct(CONSTITUENTID)) DONORCOUNT,
        count(distinct(REVENUEID)) GIFTCOUNT,
        sum(ISREGULARGIFT) REGULARGIFTCOUNT,
        count(distinct(REVENUEID)) - sum(ISREGULARGIFT) CASHGIFTCOUNT
    from APPEALREVENUE
    group by APPEALREVENUE.APPEALID
);