USP_DATALIST_SALESORDER_MEMBERSHIPS2

create procedure dbo.USP_DATALIST_SALESORDER_MEMBERSHIPS2
(
    @CONTEXTID uniqueidentifier
)
as
    set nocount on;

    declare @CURRENTDATE date = getdate();

    with MEMBERSHIP_CTE as
    (
        select
            SALESORDERITEM.ID,
            SALESORDERITEM.DESCRIPTION,
            SALESORDERITEM.PRICE,
            SALESORDERITEM.QUANTITY,
            SALESORDERITEM.TOTAL,
            coalesce(
                (
                    select top 1 MEMBER.ID
                    from dbo.MEMBER
                    where
                        MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                        and MEMBER.CONSTITUENTID = SALESORDER.CONSTITUENTID
                ),
                (
                    select top 1 MEMBER.ID
                    from dbo.MEMBER
                    where
                        MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                        and MEMBER.ISPRIMARY = 1
                        and MEMBER.ISDROPPED = 0
                )
            ) MEMBERID,
            MEMBERSHIP.ID as MEMBERSHIPID,
            case
                when exists(
                    select MEMBERSHIPCARD.ID
                    from dbo.MEMBERSHIPCARD
                    inner join.MEMBER on MEMBERSHIPCARD.MEMBERID = MEMBER.ID
                    where MEMBERSHIPCARD.STATUSCODE = 0
                        and @CURRENTDATE < MEMBERSHIPCARD.EXPIRATIONDATE
                        and MEMBER.MEMBERSHIPID = MEMBERSHIP.ID
                ) then 1
                else 0
            end HASCARDSTOPRINT,
            (
                select MEMBERSHIPTRANSACTION.REVENUESPLITID
                from dbo.MEMBERSHIPTRANSACTION
                inner join dbo.FINANCIALTRANSACTIONLINEITEM as LI on LI.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
                inner join SALESORDER on SALESORDER.REVENUEID = LI.FINANCIALTRANSACTIONID
                where
                    MEMBERSHIPTRANSACTION.MEMBERSHIPID = MEMBERSHIP.ID
                    and SALESORDER.ID = SALESORDERITEM.SALESORDERID
            ) as REVENUESPLITID,
            APPLIEDTICKETSSALESORDER.APPLIEDTOMEMBERSHIPSALESORDERID,
            case
                when APPLIEDTICKETSSALESORDER.APPLIEDTOMEMBERSHIPSALESORDERID is null then 0
                else 1
            end MEMBERSHIPHASAPPLIEDTICKETS
        from
            dbo.SALESORDER
        inner join
            dbo.SALESORDERITEM on SALESORDER.ID = SALESORDERITEM.SALESORDERID
        inner join
            dbo.SALESORDERITEMMEMBERSHIP on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIP.ID
        left join
            dbo.MEMBERSHIP on SALESORDERITEMMEMBERSHIP.MEMBERSHIPID = MEMBERSHIP.ID
        left join (
            select distinct
                SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID SALESORDERITEMMEMBERSHIPID,
                APPLIEDITEM.SALESORDERID APPLIEDTOMEMBERSHIPSALESORDERID
            from
                dbo.SALESORDERITEM
            inner join
                dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTION on SALESORDERITEM.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTION.SALESORDERITEMID
            inner join
                dbo.SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM on SALESORDERITEMMEMBERSHIPITEMPROMOTION.ID = SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.SALESORDERITEMMEMBERSHIPITEMPROMOTIONID
            inner join
                dbo.SALESORDERITEM APPLIEDITEM on SALESORDERITEMMEMBERSHIPITEMPROMOTIONAPPLIEDITEM.APPLIEDSALESORDERITEMID = APPLIEDITEM.ID
            where
                SALESORDERITEM.SALESORDERID = @CONTEXTID
        ) as APPLIEDTICKETSSALESORDER on SALESORDERITEMMEMBERSHIP.ID = APPLIEDTICKETSSALESORDER.SALESORDERITEMMEMBERSHIPID
        where
            SALESORDER.ID = @CONTEXTID
    )
    select
        ID,
        DESCRIPTION,
        PRICE,
        QUANTITY,
        TOTAL,
        MEMBERID,
        MEMBERSHIPID,
        HASCARDSTOPRINT,
        REVENUESPLITID,
        APPLIEDTOMEMBERSHIPSALESORDERID,
        MEMBERSHIPHASAPPLIEDTICKETS
    from MEMBERSHIP_CTE
    order by MEMBERSHIP_CTE.MEMBERSHIPID

    return 0;