USP_DATALIST_ORDERTICKETDETAIL

CREATE procedure dbo.USP_DATALIST_ORDERTICKETDETAIL(@ORDERID uniqueidentifier)
as
    set nocount on;

    select
        TICKET.ID,
        case 
            when SOITC.ID is not null then COMBINATION.NAME + ' - ' + SOI.DESCRIPTION
            else SOI.DESCRIPTION
        end as NAME,
        EVENT.STARTDATE as EVENTDATE,
        EVENT.STARTTIME as EVENTTIME,
        (select count(*) from dbo.TICKETPRINTINFO where TICKETID = TICKET.ID) as PRINTCOUNT,
        TICKET.TICKETNUMBER as BARCODE,
        TICKET.STATUS,
        TICKET.STATUSCODE,
        TICKET.APPLIEDTOMEMBERSHIP,
        TICKET.APPLIEDTOMEMBERSHIPSALESORDERID MEMBERSHIPSALESORDERID,
        convert(datetime, TICKET.SCANDATEWITHTIMEOFFSET) as SCANDATETIME
    from
        dbo.TICKET
    inner join
        dbo.SALESORDERITEMTICKET SOIT on TICKET.SALESORDERITEMTICKETID = SOIT.ID
    inner join
        dbo.SALESORDERITEM SOI on SOI.ID = SOIT.ID
    left join
        dbo.EVENT on SOIT.EVENTID = EVENT.ID
    left join
        dbo.SALESORDERITEMTICKETCOMBINATION SOITC on SOITC.ID = SOIT.ID
    left join
        dbo.COMBINATION on SOITC.COMBINATIONID = COMBINATION.ID
    left join
        dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMTICKET APPLIEDTICKETS on TICKET.ID = APPLIEDTICKETS.TICKETID and TICKET.APPLIEDTOMEMBERSHIP = 1
    left join
        dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM APPLIEDITEM on APPLIEDTICKETS.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEMID = APPLIEDITEM.ID
    left join
        dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION PROMOTION on APPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID = PROMOTION.ID
    left join
        dbo.SALESORDERITEM MEMBERSHIPITEM on PROMOTION.SALESORDERITEMID = MEMBERSHIPITEM.ID      
    where
        SOI.SALESORDERID = @ORDERID
        and (
            MEMBERSHIPITEM.SALESORDERID is null
            or MEMBERSHIPITEM.SALESORDERID = TICKET.APPLIEDTOMEMBERSHIPSALESORDERID
        )
    order by
        EVENTDATE,
        EVENTTIME,
        NAME,
        BARCODE;