UFN_EVENT_GETAVAILABILITYWITHEVENTS

CREATE function dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS() 
returns table
as
return
    with ORDERED_CTE as
    (
        select
            SALESORDERITEMTICKET.EVENTID,
            sum(SALESORDERITEM.QUANTITY) as ORDEREDQUANTITY
        from dbo.SALESORDERITEM
        inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
        inner join dbo.SALESORDER on SALESORDERITEM.SALESORDERID = SALESORDER.ID
        left outer join dbo.SALESORDERRESERVEDITEM on SALESORDERRESERVEDITEM.ID = SALESORDERITEMTICKET.ID
        where
            SALESORDER.STATUSCODE not in (1, 5) -- exclude complete and cancelled

            and (
                SALESORDERRESERVEDITEM.ID is null
                or (
                    SALESORDERRESERVEDITEM.EXPIRATIONDATE <= getdate()
                    and SALESORDER.STATUSCODE <> 7  -- Unresolved

                )
            )
        group by SALESORDERITEMTICKET.EVENTID
    ),
    TICKET_CTE as
    (
        select
            EVENTID,
            count(ID) as TICKETQUANTITY
        from dbo.TICKET
        where
            TICKET.STATUSCODE = 0 or
            TICKET.STATUSCODE = 1
        group by EVENTID
    )
    select
        EVENT.ID as EVENTID,
        EVENT.CAPACITY - 
            (
                coalesce(TICKET_CTE.TICKETQUANTITY,0) 
                + coalesce(ORDERED_CTE.ORDEREDQUANTITY,0)
            ) as QUANTITY
    from dbo.EVENT
    left join TICKET_CTE on
        TICKET_CTE.EVENTID = EVENT.ID
    left join ORDERED_CTE on
        ORDERED_CTE.EVENTID = EVENT.ID