USP_DATALIST_TICKETREVENUEBYDATE

            CREATE procedure dbo.USP_DATALIST_TICKETREVENUEBYDATE
            as
                set nocount on;

                with TICKETSALES_CTE as (
                    select 
                        SALESORDER.ID SALESORDERID,
                        sum([SALESORDERITEM].[TOTAL]) - coalesce(sum(CREDITITEM.TOTAL),0) - coalesce(sum(SALESORDERITEMITEMDISCOUNT.AMOUNT),0) TOTAL
                    from dbo.[SALESORDERITEM]
                    inner join dbo.[SALESORDER]
                        on [SALESORDERITEM].[SALESORDERID] = [SALESORDER].[ID]
                    inner join dbo.[SALESORDERITEMTICKET]
                        on [SALESORDERITEM].[ID] = [SALESORDERITEMTICKET].[ID]
                    left join dbo.[SALESORDERITEMITEMDISCOUNT]
                        on [SALESORDERITEM].[ID] = [SALESORDERITEMITEMDISCOUNT].[SALESORDERITEMID]
                    left join dbo.CREDITITEM 
                        on CREDITITEM.SALESORDERITEMID = SALESORDERITEM.ID
                    left outer join dbo.CREDIT 
                        on CREDITITEM.CREDITID = CREDIT.ID
                    where SALESORDER.STATUSCODE = 1
                    and (CREDITITEM.ID is null or CREDIT.TYPECODE = 0)
                    group by SALESORDER.ID
                ),
                ORDERDISCOUNT_CTE as (
                    select
                        SALESORDER.ID SALESORDERID,
                        sum(SALESORDERITEMORDERDISCOUNTDETAIL.AMOUNT) + coalesce(sum(CREDITITEM.TOTAL),0) 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
                    left outer join dbo.CREDITITEM 
                        on CREDITITEM.SALESORDERITEMID = SALESORDERITEM.ID
                    left outer join dbo.CREDIT 
                        on CREDIT.ID = CREDITITEM.CREDITID
                    where SALESORDER.STATUSCODE = 1
                    and (CREDITITEM.ID is null or CREDITITEM.TYPECODE = 5)
                    group by SALESORDER.ID
                )
                select 
                    TICKETSALES_CTE.TOTAL - coalesce(ORDERDISCOUNT_CTE.TOTAL,0),
                    SALESORDER.TRANSACTIONDATE
                from TICKETSALES_CTE
                inner join dbo.SALESORDER
                    on SALESORDER.ID = TICKETSALES_CTE.SALESORDERID
                left outer join ORDERDISCOUNT_CTE 
                    on ORDERDISCOUNT_CTE.SALESORDERID = TICKETSALES_CTE.SALESORDERID
                order by SALESORDER.TRANSACTIONDATE