Querying T_ORDER_SEAT_HIST

How about using two CTE’s? This assumes you can correctly query your current members.

To answer 1) Non-members:

SELECT DISTINCT A.customer_no

FROM CTE_OSH_Cancelled AS A

LEFT JOIN CTE_MEMBERS AS B

GROUP BY A.customer_no

To answer 2) Members:

SELECT DISTINCT A.customer_no

FROM CTE_OSH_Cancelled AS A

INNER JOIN CTE_MEMBERS AS B

GROUP BY A.customer_no

if you cannot use SSMS to find all current members, use an Extraction, List, or Analytics to get the data paste it into Excel. Turn the current member customer_no’s into a Table. Paste the customer numbers from your SSMS OSH query into Excel and turn that one into a Table. Finally, use PowerQuery to do each join version that you need. Once the data is in Excel, doing the Joins is easy.

Once you have the two distinct lists, use then to create Tessitura Lists, use them in Wordfly….

I should've just sent you an email first before coming here. This worked! Thanks Neil.

I bet there are other solutions. Using CTE's turned the issue into a join problem (easier). 

Using CTE's is often an easier way to break up the solution you want into smaller chunks. I use them when I know how to write the query that only gets me part of the data. This allows me focus only on a small part of the solution as the last part of the puzzle.

For example, you know how to query customer_no's of certain perf_no's within a prod_season and between specific perf_dt's; but you can't figure out how to find out if those customer_no's are members all within the same SSMS query. Using a CTE to hold the result-set of the query you know how to write will then allow you to only focus on the query resulting in who are current members. Once you have figure out the member part, you put that result-set into a second CTE. Finally, you only need to figure out what the two result-sets have in common and use the appropriate join (you design your second query to give you something to join on).

Another nice trick with using CTE's is:

If you create a CTE and write an Update statement that updates the CTE within the same transaction, the Update statement will only update the one column of the underlying data that shows up within the result-set of the CTE.

Since getting current memberships and cancelled tickets are things you can use in other reporting you are better off creating two parameterized table functions holding the logic this way you don't have to keep pasting the same code over again if you need to reuse the logic.

Below is an example of how we do it with ticket sales and returns. Because Sales and returns have different logic we put them into separate functions that we can call individually or as one via this function. We opted for the third function to be a table as it is still a work in progress and there will be some additional logic in place to handle some additional functionality.

IF OBJECT_ID (N'dbo.LFT_MBA_Tkt_Data', N'TF') IS NULL
begin
    Declare @SQL varchar(max) =
    'CREATE FUNCTION dbo.LFT_MBA_Tkt_Data()

    returns
     @tbl table (
    ID            int
    primary key (ID))
    as begin
        insert into @tbl
        Select 1
        return
    end'
    execute (@SQl)
end
GO  
alter FUNCTION dbo.LFT_MBA_Tkt_Data(

@StartDate        date  --= '03/12/2020'
,@EndDate        datetime
,@OriginIDs        varchar(Max) --= 1
,@PriceTypeIds    varchar(Max)  --= '140,104,61'
,@OrderIDs        varchar(Max) --= '2293267'
,@PerfIDs        varchar(Max) --= '6361,3937,6358'
,@OnlyZeroDollar    bit
)
returns
 @tbl table (
    [RecType] [varchar](10) NOT NULL,
    [Order_no] [int] NULL,
    [Sli_No] [int] NOT NULL,
    [TranDate] [date] NULL,
    [TranTime] [datetime] NULL,
    [Perf_no] [int] NULL,
    [PriceTypeID] [int] NULL,
    [price] [money] NULL,
    [Amount] [money] NULL,
    [qty] [int] NOT NULL,
    [ReturnQty] [int] NOT NULL,
    [SalesQty] [int] NOT NULL,
    [zone_no] [int] NULL,
    [SLIStatusID] [int] NULL,
    [CompCode] [int] NOT NULL,
    [batch_no] [int] NULL,
    [OriginID] [int] NOT NULL,
    [Origin] [varchar](30) NOT NULL,
    [machine_name] [varchar](30) NULL,
    [OriginalTicketNo] [int] NOT NULL,
    [CurrentTicketNo] [int] NULL,
    [seat_no] [int] NULL,
    [printed_by] [varchar](50) NULL,
    [RecipientNo] [int] NOT NULL,
    [ret_parent_sli_no] [int] NOT NULL
    ,ZeroDollar        bit
    primary Key (Sli_no))
as begin


    insert into @tbl
    Select * from LFT_MBA_Tkt_Sales(@StartDate , @EndDate , @OriginIDs  , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
    union Select * from LFT_MBA_Tkt_Returns(@StartDate , @EndDate , @OriginIDs , @PriceTypeIds , @OrderIDs , @PerfIDs,@OnlyZeroDollar  )
    return
end

Thank you, Ronald! I didn't see that this had been posted. I'm excited to dig into what you've provided here so we can optimize this process for future needs.