UFN_SALESORDER_GETTOTALTICKETREVENUE

        CREATE function dbo.UFN_SALESORDER_GETTOTALTICKETREVENUE()
        returns money
        with execute as caller
        as begin
            declare @TICKETTOTAL money;
            declare @ORDERDISCOUNTTOTAL money;

            ;with SALESORDER_CTE as (
                select
                    [SALESORDERITEM].[ID],
                    [SALESORDERITEM].[TOTAL] as [AMOUNT]
                from dbo.[SALESORDERITEM]
                left join dbo.[SALESORDER]
                    on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
                inner join dbo.[SALESORDERITEMTICKET]
                    on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                where [SALESORDER].[STATUSCODE] in (1)
            ),
            REFUNDS_CTE as (
                select
                    sum([CREDITITEM].[TOTAL]) - isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0) as [AMOUNT]
                from dbo.[CREDITITEM]
                inner join SALESORDER_CTE
                    on [SALESORDER_CTE].[ID] = [CREDITITEM].[SALESORDERITEMID]
                left join dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
                    on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [CREDITITEM].[SALESORDERITEMID]
            ),
            ITEMDISCOUNTS_CTE as (
                select
                    sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]) as [AMOUNT]
                from dbo.[SALESORDERITEMITEMDISCOUNT]
                inner join SALESORDER_CTE on SALESORDER_CTE.ID = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
            )
            select
                @TICKETTOTAL = (sum(SALESORDER_CTE.AMOUNT)) - coalesce((select AMOUNT from REFUNDS_CTE), 0) - coalesce((select AMOUNT from ITEMDISCOUNTS_CTE), 0)
            from SALESORDER_CTE

            select
                @ORDERDISCOUNTTOTAL = (sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT))
            from dbo.SALESORDERITEMORDERDISCOUNTDETAIL
            inner join dbo.SALESORDERITEM 
                on SALESORDERITEM.ID = SALESORDERITEMORDERDISCOUNTDETAIL.SALESORDERITEMID
            inner join dbo.SALESORDER 
                on SALESORDER.ID = SALESORDERITEM.SALESORDERID
            inner join dbo.SALESORDERITEMTICKET
                on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
            where SALESORDER.STATUSCODE in (1)

            return coalesce(@TICKETTOTAL, 0) - coalesce(@ORDERDISCOUNTTOTAL, 0)
        end