V_PROGRAMEVENT_TICKETCOUNTS

CREATE view dbo.V_PROGRAMEVENT_TICKETCOUNTS as
    with TICKETSREFUNDEDANDCANCELLED_CTE as (
        select 
            EVENTID,
            count(case when ISREFUNDED = 1 then 1 else null end) as REFUNDED,
            count(case when STATUSCODE = 2 then 1 else null end) as CANCELLED
        from 
            dbo.TICKET
        where
            EVENTID is not null
        group by
            EVENTID
    ),
    TICKETCOUNTS_CTE as (
        select
            SALESORDERITEMTICKET.EVENTID,
            sum(case when SALESORDER.STATUSCODE = 1 then SALESORDERITEM.QUANTITY else 0 end) as SOLD,
            sum(
                case
                    when SALESORDER.STATUSCODE not in (1, 5) /* Complete, Cancelled */ and (SALESORDER.STATUSCODE = 7 /* Unresolved */ or SALESORDERRESERVEDITEM.ID is null) then
                        SALESORDERITEM.QUANTITY
                    else
                        0
                end
            ) as INORDER,
            sum(case when SALESORDER.STATUSCODE = 6 then SALESORDERITEM.QUANTITY else 0 end) as RESERVED,
            sum(case when SALESORDER.STATUSCODE in (3,4) then SALESORDERITEM.QUANTITY else 0 end) as CONFIRMEDORFINALIZED
        from
            dbo.SALESORDERITEMTICKET
        inner join
            dbo.SALESORDERITEM on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join
            dbo.SALESORDER on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        left outer join
            dbo.SALESORDERRESERVEDITEM on SALESORDERRESERVEDITEM.ID = SALESORDERITEMTICKET.ID and SALESORDERRESERVEDITEM.EXPIRATIONDATE <= getdate()
        group by
            SALESORDERITEMTICKET.EVENTID
    )
    select
        EVENT.ID,
        EVENT.PROGRAMID,
        isnull(TICKETCOUNTS_CTE.SOLD, 0) - isnull(TICKETSREFUNDEDANDCANCELLED_CTE.CANCELLED, 0) as SOLD,
        isnull(TICKETSREFUNDEDANDCANCELLED_CTE.REFUNDED, 0) as REFUNDED,
        isnull(TICKETCOUNTS_CTE.INORDER, 0) as INORDER,
        (isnull(TICKETCOUNTS_CTE.SOLD, 0) + isnull(TICKETCOUNTS_CTE.INORDER, 0) - isnull(TICKETSREFUNDEDANDCANCELLED_CTE.CANCELLED, 0)) as INUSE,
        (EVENT.CAPACITY - isnull(TICKETCOUNTS_CTE.SOLD, 0) + isnull(TICKETSREFUNDEDANDCANCELLED_CTE.CANCELLED, 0) - isnull(TICKETCOUNTS_CTE.INORDER, 0)) as AVAILABILITY,
        isnull(TICKETCOUNTS_CTE.RESERVED, 0) as RESERVED,
        isnull(TICKETCOUNTS_CTE.CONFIRMEDORFINALIZED, 0) as CONFIRMEDORFINALIZED,
        isnull(TICKETSREFUNDEDANDCANCELLED_CTE.CANCELLED, 0) as CANCELLED
    from
        dbo.EVENT
    left outer join
        TICKETCOUNTS_CTE on TICKETCOUNTS_CTE.EVENTID = EVENT.ID
    left outer join
        TICKETSREFUNDEDANDCANCELLED_CTE on TICKETSREFUNDEDANDCANCELLED_CTE.EVENTID = EVENT.ID
    where
        EVENT.PROGRAMID is not null;