UFN_DESIGNATIONLEVEL_REVENUECOUNTS

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS
            (
                @STARTDATE datetime,
                @ENDDATE datetime,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )            
            returns table
            as
            return(
                    select SUBSEL.DESIGNATIONLEVELID,
                            count(distinct R.CONSTITUENTID) NUMDONORS,
                            count(distinct SUBSEL.REVENUEID) NUMGIFTS,
                            max(SUBSEL.SPLITSTODESIGNATIONSUM) MAXGIFT,
                            sum(SUBSEL.SPLITSTODESIGNATIONSUM) TOTALGIFT
                    from 
                        (
                            select DL.ID DESIGNATIONLEVELID,
                                    R.ID REVENUEID,
                                    sum(RS.AMOUNT) - coalesce((select sum(WOS.AMOUNT) from dbo.WRITEOFF WO inner join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID where WO.REVENUEID = R.ID), 0) SPLITSTODESIGNATIONSUM
                            from dbo.DESIGNATIONLEVEL DL
                                inner join dbo.DESIGNATION D on (D.DESIGNATIONLEVEL1ID = DL.ID or
                                    D.DESIGNATIONLEVEL2ID = DL.ID or
                                    D.DESIGNATIONLEVEL3ID = DL.ID or             
                                    D.DESIGNATIONLEVEL4ID = DL.ID or
                                    D.DESIGNATIONLEVEL5ID = DL.ID)
                                inner join dbo.REVENUESPLIT RS on D.ID = RS.DESIGNATIONID
                                inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                                left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
                                left join dbo.REVENUE IREV on IREV.ID = ISP.PLEDGEID
                            where (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                (dbo.UFN_REVENUE_HASDESIGNATION_PLANNEDGIFTLIKEPLEDGE(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1 or
                                (R.TRANSACTIONTYPECODE = 0 and RS.APPLICATIONCODE = 7 and IREV.TRANSACTIONTYPECODE is null)) and
                                (
                                    @CAMPAIGNHIERARCHYPATH is null or
                                    exists (
                                        select 
                                            REVENUESPLITCAMPAIGN.REVENUESPLITID
                                        from 
                                            dbo.REVENUESPLITCAMPAIGN
                                            inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                        where 
                                            REVENUESPLITCAMPAIGN.REVENUESPLITID = RS.ID and
                                            CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                    )
                                )
                            group by R.ID, DL.ID
                        ) SUBSEL inner join dbo.REVENUE R on R.ID = SUBSEL.REVENUEID
                    group by SUBSEL.DESIGNATIONLEVELID
            )