UFN_REPORT_VSECATEGORY

            CREATE function dbo.UFN_REPORT_VSECATEGORY(
            @STARTDATE datetime = null,
            @ENDDATE datetime = null, 
            @USEGIFTDATE smallint = 0
            )
            returns table as
            return (

                select 
                    d.name, 
                    d.vsecategoryid,
                    r.amount "revenueamount", 
                    rs.amount "revenuesplitamount",
                    r.id "revenueid", 
                    rs.id "revenuesplitid", 
                    d.id "designationid",
                    --desg.highestlevel,

                    --desg.highestleveldesignation,

                    r.date, 
                    r.postdate,
                    r.constituentid,
                    r.transactiontype,
                    r.transactiontypecode,
                    rp.paymentmethodcode,
                    rs.applicationcode
                from 
                    dbo.revenue r with (nolock)
                    inner join dbo.revenuepaymentmethod rp on rp.revenueid = r.id
                    inner join dbo.revenuesplit rs on rs.revenueid = r.id
                    inner join dbo.designation d on d.id = rs.designationid
                /*
                    (
                        select 
                        id, 
                        case when designationlevel5id is not null then 5 
                            else case when designationlevel4id is not null then 4
                                else case when designationlevel3id is not null then 3
                                    else case when designationlevel2id is not null then 2
                                        else case when designationlevel1id is not null then 1
                                            else null 
                                            end
                                        end
                                    end
                                end
                            end  "highestlevel",
                        case when designationlevel5id is not null then designationlevel5id 
                            else case when designationlevel4id is not null then designationlevel4id
                                else case when designationlevel3id is not null then designationlevel3id
                                    else case when designationlevel2id is not null then designationlevel2id
                                        else case when designationlevel1id is not null then designationlevel1id
                                            else null 
                                            end
                                        end
                                    end
                                end
                            end  "highestleveldesignation"
                        from dbo.designation
                    ) desg on desg.id = rs.designationid
                    inner join 
                    dbo.designationlevel dl on dl.id= desg.highestleveldesignation
                */
                where r.transactiontypecode not in (2, 3) and 
                    (case when @STARTDATE is null then 
                        0
                    else 
                        datediff(day, @STARTDATE, case when @USEGIFTDATE <> 0 then r.date else isnull(r.postdate, r.date) end) end) >= 0

                    and (case when @STARTDATE is null then 
                        0
                    else 
                        datediff(day, @ENDDATE, case when @USEGIFTDATE <> 0 then r.date else isnull(r.postdate, r.date) end) end) <= 0
                    and d.vsecategoryid is not null
            )