UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYPERIOD

            create function dbo.UFN_DESIGNATIONLEVEL_REVENUEPLEDGED_BYPERIOD
            (
                @STARTDATE datetime, 
                @ENDDATE datetime, 
                @PERIODOPTION tinyint,
                @CAMPAIGNHIERARCHYPATH hierarchyid = null
            )
            returns table
            as
            return(
                select DL.ID DESIGNATIONLEVELID,
                        PERIODS.SEQUENCE,
                        PERIODS.PERIODYEAR,
                        PERIODS.STARTDATE,
                        PERIODS.ENDDATE,
                        sum(PLEDGESPLIT.AMOUNT) TOTALPLEDGED,
                        (select coalesce(sum(PAYSPLIT.AMOUNT), 0) 
                         from dbo.REVENUESPLIT PAYSPLIT 
                            inner join dbo.REVENUE PAY on PAYSPLIT.REVENUEID = PAY.ID
                            inner join dbo.DESIGNATION DP on PAYSPLIT.DESIGNATIONID = DP.ID
                         where PAYSPLIT.ID in (SELECT IP.PAYMENTID
                                    from dbo.INSTALLMENTPAYMENT IP
                                    inner join dbo.REVENUE PLEDGE on IP.PLEDGEID = PLEDGE.ID
                                    inner join dbo.REVENUESPLIT PLEDGESPLIT on PLEDGE.ID = PLEDGESPLIT.REVENUEID
                                    inner join dbo.DESIGNATION DPL on PLEDGESPLIT.DESIGNATIONID = DPL.ID
                                    where(PLEDGE.DATE >= PERIODS.STARTDATE and PLEDGE.DATE <= PERIODS.ENDDATE) and
                                    (DPL.DESIGNATIONLEVEL1ID = DL.ID or
                                     DPL.DESIGNATIONLEVEL2ID = DL.ID or
                                     DPL.DESIGNATIONLEVEL3ID = DL.ID or             
                                     DPL.DESIGNATIONLEVEL4ID = DL.ID or
                                     DPL.DESIGNATIONLEVEL5ID = DL.ID) and
                                    (
                                        @CAMPAIGNHIERARCHYPATH is null or
                                        exists (
                                            select 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID
                                            from 
                                                dbo.REVENUESPLITCAMPAIGN
                                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                            where 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID = PLEDGESPLIT.ID and
                                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                        )
                                    )
                        )
                        and (PAY.DATE >= PERIODS.STARTDATE and PAY.DATE <= PERIODS.ENDDATE) and
                        (DP.DESIGNATIONLEVEL1ID = DL.ID or
                         DP.DESIGNATIONLEVEL2ID = DL.ID or
                         DP.DESIGNATIONLEVEL3ID = DL.ID or             
                         DP.DESIGNATIONLEVEL4ID = DL.ID or
                         DP.DESIGNATIONLEVEL5ID = DL.ID) and
                                    (
                                        @CAMPAIGNHIERARCHYPATH is null or
                                        exists (
                                            select 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID
                                            from 
                                                dbo.REVENUESPLITCAMPAIGN
                                                inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                            where 
                                                REVENUESPLITCAMPAIGN.REVENUESPLITID = PAYSPLIT.ID and
                                                CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                                        )
                                    )
                        ) TOTALPAID,
                        (select coalesce(sum(WOS.AMOUNT), 0) 
                        from dbo.WRITEOFFSPLIT WOS
                        inner join dbo.DESIGNATION WOSD on WOS.DESIGNATIONID = WOSD.ID
                        inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
                        inner join dbo.REVENUE WOREV on WO.REVENUEID = WOREV.ID
                        where 
                            (WOSD.DESIGNATIONLEVEL1ID = DL.ID or
                             WOSD.DESIGNATIONLEVEL2ID = DL.ID or
                             WOSD.DESIGNATIONLEVEL3ID = DL.ID or             
                             WOSD.DESIGNATIONLEVEL4ID = DL.ID or
                             WOSD.DESIGNATIONLEVEL5ID = DL.ID) and
                            (WOREV.DATE >= PERIODS.STARTDATE) and
                            (WOREV.DATE <= PERIODS.ENDDATE) and
                            WOREV.TRANSACTIONTYPECODE in (1,3,4,6)) TOTALWRITEOFFS
                    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 PLEDGESPLIT on D.ID = PLEDGESPLIT.DESIGNATIONID
                    inner join dbo.REVENUE PLEDGE on PLEDGESPLIT.REVENUEID = PLEDGE.ID
                    inner join dbo.UFN_DESIGNATIONREPORT_GETPERIODS(dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),dbo.UFN_DATE_GETLATESTTIME(@ENDDATE),@PERIODOPTION) PERIODS on PLEDGE.DATE >= PERIODS.STARTDATE and PLEDGE.DATE <= PERIODS.ENDDATE
                    where (PLEDGE.TRANSACTIONTYPECODE in (1,3,4,6)) and
                        (
                            @CAMPAIGNHIERARCHYPATH is null or
                            exists (
                                select 
                                    REVENUESPLITCAMPAIGN.REVENUESPLITID
                                from 
                                    dbo.REVENUESPLITCAMPAIGN
                                    inner join dbo.CAMPAIGN on CAMPAIGN.ID = REVENUESPLITCAMPAIGN.CAMPAIGNID
                                where 
                                    REVENUESPLITCAMPAIGN.REVENUESPLITID = PLEDGESPLIT.ID and
                                    CAMPAIGN.HIERARCHYPATH.IsDescendantOf(@CAMPAIGNHIERARCHYPATH)=1
                            )
                        )
                    group by DL.ID, PERIODS.PERIODYEAR,PERIODS.STARTDATE,PERIODS.ENDDATE,PERIODS.SEQUENCE 

            )