UFN_DESIGNATION_REVENUECOUNTS_2

create function dbo.UFN_DESIGNATION_REVENUECOUNTS_2(
    @STARTDATETIME datetime, 
    @ENDDATETIME datetime
)
returns @REVENUEINFO table
(
    DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
    NUMDONORS int NOT NULL,
    NUMGIFTS int NOT NULL,
    MAXGIFT money NOT NULL,
    NUMREFUNDED int NOT NULL,
    MAXREFUND money NOT NULL
)
as
begin
    declare @STARTDATE datetimeoffset(3) = isnull(@STARTDATETIME, '1753-01-01')
    declare @ENDDATE datetimeoffset(3) = isnull(dateadd(day, 1, @ENDDATETIME), '9999-12-31')

    insert @REVENUEINFO
        select 
            REVENUEINFO.DESIGNATIONID,
            count(distinct FINANCIALTRANSACTION.CONSTITUENTID),
            count(distinct REVENUEINFO.REVENUEID),
            max(REVENUEINFO.SPLITSTODESIGNATIONSUM),
            sum(REFUNDCOUNT),
            max(REFUNDAMOUNT)
        from (
            select 
                D.ID DESIGNATIONID,
                FINANCIALTRANSACTION.ID REVENUEID,
                sum(REVENUELINEITEM.BASEAMOUNT) - coalesce((select sum(WOS.BASEAMOUNT) from dbo.FINANCIALTRANSACTION WO inner join dbo.FINANCIALTRANSACTIONLINEITEM WOS on WO.ID = WOS.FINANCIALTRANSACTIONID where WO.TYPECODE = 20 and WOS.DELETEDON is null and WO.PARENTID = FINANCIALTRANSACTION.ID), 0) SPLITSTODESIGNATIONSUM,
                count(distinct REFUNDLINEITEM.SOURCELINEITEMID) as REFUNDCOUNT,
                isnull(max(REFUNDLINEITEM.AMOUNT),0) as REFUNDAMOUNT
            from dbo.DESIGNATION D
            inner join dbo.DESIGNATION D2 on (D.DESIGNATIONLEVEL1ID = D2.DESIGNATIONLEVEL1ID
                and (D.DESIGNATIONLEVEL2ID = D2.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                and (D.DESIGNATIONLEVEL3ID = D2.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                and (D.DESIGNATIONLEVEL4ID = D2.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                and (D.DESIGNATIONLEVEL5ID = D2.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null))
            inner join dbo.REVENUESPLIT_EXT RS on RS.DESIGNATIONID = D2.ID
            inner join dbo.FINANCIALTRANSACTIONLINEITEM REVENUELINEITEM on 
                REVENUELINEITEM.ID = RS.ID
            inner join dbo.FINANCIALTRANSACTION on 
                REVENUELINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            left join (
                --Refunds for donations made in this time period. These refunds could have been made outside of the time period

                --Right now, only a full refund can be made against a donation in sales. Not making that assumption here: using subquery and grouping on source line item.

                --However, I am counting all the refunds toward a donation as one refund.

                select 
                    FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID,
                    sum(FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT) as AMOUNT
                from dbo.FINANCIALTRANSACTIONLINEITEM
                inner join dbo.CREDITITEM_EXT on 
                    FINANCIALTRANSACTIONLINEITEM.ID = CREDITITEM_EXT.ID
                inner join dbo.FINANCIALTRANSACTION REFUND on
                    FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = REFUND.ID
                where REFUND.TYPECODE = 23
                group by FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID
            ) as REFUNDLINEITEM on
                REVENUELINEITEM.ID = REFUNDLINEITEM.SOURCELINEITEMID
            where 
                FINANCIALTRANSACTION.DELETEDON is null and
                FINANCIALTRANSACTION.DATE >= @STARTDATE and
                FINANCIALTRANSACTION.DATE < @ENDDATE and
                (
                    --Donation, event registration (donation), recurring gift payment, planned gift payment, matching gift payment, and donor challenge payments

                    (FINANCIALTRANSACTION.TYPECODE = 0 and RS.APPLICATIONCODE in (0,1,3,6,7,13)) or
                    --pledges or orders or grants or auction donations

                    (FINANCIALTRANSACTION.TYPECODE in (1,5,6,7))
                )
            group by FINANCIALTRANSACTION.ID, D.ID
        ) REVENUEINFO
        inner join dbo.FINANCIALTRANSACTION on 
            FINANCIALTRANSACTION.ID = REVENUEINFO.REVENUEID
        group by REVENUEINFO.DESIGNATIONID
    return
end