UFN_DESIGNATION_PLANNEDGIFTREVENUE

            create function dbo.UFN_DESIGNATION_PLANNEDGIFTREVENUE
            (
                @STARTDATE datetime, 
                @ENDDATE datetime
            )
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL PRIMARY KEY CLUSTERED,
                TOTALPLANNEDGIFT money NOT NULL
            )
            as begin

                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert @REVENUEINFO
                select 
                    D.ID,
                    sum(PGSPLIT.AMOUNT)
                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 PGSPLIT on D2.ID = PGSPLIT.DESIGNATIONID
                inner join dbo.REVENUE PG on PGSPLIT.REVENUEID = PG.ID
                where (PG.DATE >= @STARTDATE) and
                        (PG.DATE <= @ENDDATE) and
                        (PG.TRANSACTIONTYPECODE = 4)

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

                return
            end