UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL

            CREATE function dbo.UFN_DESIGNATION_REVENUECOUNTSBYAPPEAL
            (@STARTDATE datetime, @ENDDATE datetime)
            returns @REVENUEINFO table
            (
                DESIGNATIONID uniqueidentifier NOT NULL,
                APPEALID uniqueidentifier,
                NUMDONORS int NOT NULL,
                NUMGIFTS int NOT NULL,
                MAXGIFT money NOT NULL
            )
            as
            begin
                select @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE),
                        @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);

                insert @REVENUEINFO
                    select SUBSEL.DESIGNATIONID,
                            SUBSEL.REVENUEAPPEALID,
                            count(distinct R.CONSTITUENTID),
                            count(distinct SUBSEL.REVENUEID),
                            max(SUBSEL.SPLITSTODESIGNATIONSUM)
                    from 
                        (
                            select D.ID DESIGNATIONID,
                                    R.APPEALID REVENUEAPPEALID,
                                    R.ID REVENUEID,
                                    sum(RS.AMOUNT - coalesce(WOS.AMOUNT, 0)) SPLITSTODESIGNATIONSUM
                            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 RS on RS.DESIGNATIONID = D2.ID
                                inner join dbo.REVENUE R on RS.REVENUEID = R.ID
                                left join dbo.WRITEOFF WO on R.ID = WO.REVENUEID 
                                left join dbo.WRITEOFFSPLIT WOS on WO.ID = WOS.WRITEOFFID and RS.DESIGNATIONID = WOS.DESIGNATIONID                
                            where (R.DATE >= @STARTDATE or @STARTDATE is null) and
                                    (R.DATE <= @ENDDATE or @ENDDATE is null) and
                                    (
                                        --Donation, recurring gift payment, planned gift payment, matching gift payment, donor challenge payment

                                        (R.TRANSACTIONTYPECODE = 0 and (RS.APPLICATIONCODE in (0,3,6,7,13) or (RS.APPLICATIONCODE = 1 and RS.TYPECODE = 0)))
                                        or
                                        --pledges or grants or auction donations

                                        (R.TRANSACTIONTYPECODE in (1,6,7))
                                    )
                            group by R.ID, D.ID, R.APPEALID
                        ) SUBSEL inner join dbo.REVENUE R on R.ID = SUBSEL.REVENUEID
                    group by SUBSEL.DESIGNATIONID, SUBSEL.REVENUEAPPEALID
                return
            end