UFN_DESIGNATION_NEWCOMMITMENTREVENUE

            create function dbo.UFN_DESIGNATION_NEWCOMMITMENTREVENUE
            (
                @STARTDATE datetime, 
                @ENDDATE datetime
            )
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                TOTALNEWCOMMITMENT money NOT NULL,
                TOTALPAID money NOT NULL,
                TOTALWRITEOFFS money NOT NULL
            )
            AS
            BEGIN
                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert @REVENUEINFO
                select 
                    D.ID,
                    sum(SPLIT.AMOUNT),
                    (
                        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 >= @STARTDATE or @STARTDATE is null) and
                                                        (PLEDGE.DATE <= @ENDDATE or @ENDDATE is null) and 
                                                        (D.DESIGNATIONLEVEL1ID = DPL.DESIGNATIONLEVEL1ID
                                                                and (D.DESIGNATIONLEVEL2ID = DPL.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                                                and (D.DESIGNATIONLEVEL3ID = DPL.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                                                and (D.DESIGNATIONLEVEL4ID = DPL.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                                                and (D.DESIGNATIONLEVEL5ID = DPL.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) 
                                                        and PLEDGESPLIT.APPLICATIONCODE in (2,8)
                                             )
                        and
                        (D.DESIGNATIONLEVEL1ID = DP.DESIGNATIONLEVEL1ID
                                and (D.DESIGNATIONLEVEL2ID = DP.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                and (D.DESIGNATIONLEVEL3ID = DP.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                and (D.DESIGNATIONLEVEL4ID = DP.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                and (D.DESIGNATIONLEVEL5ID = DP.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) and 
                        (PAY.DATE >= @STARTDATE) and
                        (PAY.DATE <= @ENDDATE)
                     ),
                    (
                        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 
                            (WOREV.DATE >= @STARTDATE or @STARTDATE is null) and
                            (WOREV.DATE <= @ENDDATE or @ENDDATE is null) and
                            (D.DESIGNATIONLEVEL1ID = WOSD.DESIGNATIONLEVEL1ID
                                and (D.DESIGNATIONLEVEL2ID = WOSD.DESIGNATIONLEVEL2ID or D.DESIGNATIONLEVEL2ID is null)
                                and (D.DESIGNATIONLEVEL3ID = WOSD.DESIGNATIONLEVEL3ID or D.DESIGNATIONLEVEL3ID is null)
                                and (D.DESIGNATIONLEVEL4ID = WOSD.DESIGNATIONLEVEL4ID or D.DESIGNATIONLEVEL4ID is null)
                                and (D.DESIGNATIONLEVEL5ID = WOSD.DESIGNATIONLEVEL5ID or D.DESIGNATIONLEVEL5ID is null)) and
                            (WOREV.TRANSACTIONTYPECODE in (1,6))
                    )
                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 SPLIT on D2.ID = SPLIT.DESIGNATIONID
                    inner join dbo.REVENUE on SPLIT.REVENUEID = REVENUE.ID
                where 
                    (REVENUE.DATE >= @STARTDATE) and
                    (REVENUE.DATE <= @ENDDATE) and
                    (REVENUE.TRANSACTIONTYPECODE in (1,6))

                group by D.ID, D.DESIGNATIONLEVEL1ID, D.DESIGNATIONLEVEL2ID, D.DESIGNATIONLEVEL3ID, D.DESIGNATIONLEVEL4ID, D.DESIGNATIONLEVEL5ID

               return
            end