USP_DATALIST_TICKETREVENUEBYPROGRAM

            CREATE procedure dbo.USP_DATALIST_TICKETREVENUEBYPROGRAM
            as
                set nocount on;

                with SALESORDERITEMS_CTE as
                (
                    select
                        [SALESORDERITEM].[ID] as [SALESORDERITEMID],
                        [PROGRAM].[ID] [PROGRAMID]
                    from dbo.[SALESORDERITEM]
                    inner join dbo.[SALESORDER]
                        on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
                    inner join dbo.[SALESORDERITEMTICKET]
                        on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                    inner join dbo.[PROGRAM]
                        on [PROGRAM].[ID] = [SALESORDERITEMTICKET].[PROGRAMID]
                    where [SALESORDER].[STATUSCODE] = 1
                ),
                TICKETSALES_CTE as (
                    select
                        sum([SALESORDERITEM].[TOTAL]) [TOTAL],
                        [SALESORDERITEMS_CTE].[PROGRAMID]
                    from dbo.[SALESORDERITEM]
                    inner join [SALESORDERITEMS_CTE]
                        on [SALESORDERITEMS_CTE].[SALESORDERITEMID] = [SALESORDERITEM].[ID]
                    group by [SALESORDERITEMS_CTE].[PROGRAMID]
                ),
                REFUNDS_CTE as (
                    select
                        isnull(sum([CREDITITEM].[TOTAL]), 0) - isnull(sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]), 0) as [TOTAL],
                        [SALESORDERITEMS_CTE].[PROGRAMID]
                    from dbo.[CREDITITEM]
                    inner join [SALESORDERITEMS_CTE]
                        on [SALESORDERITEMS_CTE].[SALESORDERITEMID] = [CREDITITEM].[SALESORDERITEMID]
                    left join dbo.[SALESORDERITEMORDERDISCOUNTDETAIL]
                        on [SALESORDERITEMORDERDISCOUNTDETAIL].[SALESORDERITEMID] = [CREDITITEM].[SALESORDERITEMID]
                    group by [SALESORDERITEMS_CTE].[PROGRAMID]
                ),
                ITEMDISCOUNTS_CTE as (
                    select
                        sum([SALESORDERITEMITEMDISCOUNT].[AMOUNT]) as [TOTAL],
                        [SALESORDERITEMS_CTE].[PROGRAMID]
                    from dbo.[SALESORDERITEMITEMDISCOUNT]
                    inner join [SALESORDERITEMS_CTE]
                        on [SALESORDERITEMS_CTE].[SALESORDERITEMID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
                    group by [SALESORDERITEMS_CTE].[PROGRAMID]
                ),
                ORDERDISCOUNT_CTE as (
                    select
                        [PROGRAM].[ID] [PROGRAMID],
                        sum([SALESORDERITEMORDERDISCOUNTDETAIL].[AMOUNT]) [TOTAL]
                    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]
                    inner join dbo.[PROGRAM]
                        on [PROGRAM].[ID] = [SALESORDERITEMTICKET].[PROGRAMID]
                    where [SALESORDER].[STATUSCODE] = 1
                    group by [PROGRAM].[ID]
                )
                select
                    PROGRAM.NAME PROGRAMNAME,
                    isnull([TICKETSALES_CTE].[TOTAL], 0) - isnull([ORDERDISCOUNT_CTE].[TOTAL],0) - isnull([ITEMDISCOUNTS_CTE].[TOTAL],0) - isnull([REFUNDS_CTE].[TOTAL],0) [TOTALREVENUE]
                from [TICKETSALES_CTE]
                inner join dbo.[PROGRAM]
                    on [PROGRAM].[ID] = [TICKETSALES_CTE].[PROGRAMID]
                left outer join [ORDERDISCOUNT_CTE]
                    on [ORDERDISCOUNT_CTE].[PROGRAMID] = [TICKETSALES_CTE].[PROGRAMID]
                left outer join [ITEMDISCOUNTS_CTE]
                    on [ITEMDISCOUNTS_CTE].[PROGRAMID] = [TICKETSALES_CTE].[PROGRAMID]
                left outer join [REFUNDS_CTE]
                    on [REFUNDS_CTE].[PROGRAMID] = [TICKETSALES_CTE].[PROGRAMID]