UFN_DESIGNATIONLEVEL_GETREVENUETOTALS

            create function dbo.UFN_DESIGNATIONLEVEL_GETREVENUETOTALS
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null
            )
            returns table
            as
            return(
                with DESIGNATIONS_CTE as (
                    select ID 
                    from dbo.DESIGNATION D
                    where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID or 
                          D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID or
                          D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID
                    )
                    select
                        coalesce(sum(DATA.TOTALREVENUE), 0) as TOTALREVENUE,
                        count(distinct DATA.CONSTITUENTID) as NUMDONORS,
                        count(distinct DATA.REVENUEID) as NUMGIFTS
                    from
                        (
                            select
                                coalesce(
                                    (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
                                        )
                                    ),0
                                ) TOTALREVENUE,
                                R.CONSTITUENTID,
                                R.ID as REVENUEID
                            from
                                dbo.REVENUESPLIT RS
                                inner join dbo.REVENUE R on R.ID = RS.REVENUEID                    
                                inner join DESIGNATIONS_CTE D on RS.DESIGNATIONID = D.ID
                            where 
                                (R.DATE >= dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) or @STARTDATE is null) and
                                (R.DATE <= dbo.UFN_DATE_GETLATESTTIME(@ENDDATE) or @ENDDATE is null) and
                                dbo.UFN_REVENUE_HASDESIGNATION(R.TRANSACTIONTYPECODE, RS.APPLICATIONCODE) = 1
                            group by
                                R.CONSTITUENTID, R.ID
                        ) as DATA
            )