UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYPERIOD

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUECOUNTS_BYPERIOD
            (
                @STARTDATE datetime,
                @ENDDATE datetime,
                @PERIODOPTION tinyint,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )            
            returns table
            as
            return(
                    select SUBSEL.DESIGNATIONLEVELID,
                        PERIODS.SEQUENCE,
                        PERIODS.PERIODYEAR,
                        PERIODS.STARTDATE,
                        PERIODS.ENDDATE,
                        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
                                inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS( dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),dbo.UFN_DATE_GETLATESTTIME(@ENDDATE),@PERIODOPTION) PERIODS on R.DATE >= PERIODS.STARTDATE and R.DATE <= PERIODS.ENDDATE
                                left join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = RS.ID
                                left join dbo.REVENUE IREV on IREV.ID = ISP.PLEDGEID
                            where (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, PERIODS.PERIODYEAR,PERIODS.STARTDATE,PERIODS.ENDDATE,PERIODS.SEQUENCE 
                        )  SUBSEL inner join dbo.REVENUE R on R.ID = SUBSEL.REVENUEID
                        inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS( dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),dbo.UFN_DATE_GETLATESTTIME(@ENDDATE),@PERIODOPTION) PERIODS on R.DATE >= PERIODS.STARTDATE and R.DATE <= PERIODS.ENDDATE
                    group by SUBSEL.DESIGNATIONLEVELID, PERIODS.PERIODYEAR,PERIODS.STARTDATE,PERIODS.ENDDATE,PERIODS.SEQUENCE

            )