Hello,
I'm trying to query T_ORDER_SEAT_HIST and use the query to make 2 lists:
We do not print tickets for summer camps, so we could not "return" them, but had to delete SLI's to generate the refund, hence T_ORDER_SEAT_HIST.
I can get a straight list of constituent ID's, no problem. Adding the dimension of their membership current status seems to throw everything into disarray. Anyone have any ideas?
Here's where I start. I don't have any of my attempts at linking a membership table/view.
select distinct osh.order_no, osh.customer_no from T_ORDER_SEAT_HIST oshjoin T_PERF pon p.perf_no = osh.perf_nojoin T_PROD_SEASON pson ps.prod_season_no = p.prod_season_nojoin T_INVENTORY ion osh.perf_no = i.inv_no
where ps.season in ('250','251') <The two season ID's for the seasons that our camp prod seasons live in; our FY ends in June so our offerings are split into two seasons.>and p.perf_type = 1and osh.event_code = 26
Any insight from the community is greatly appreciated!
Michael Dorsey
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:
INNER JOIN CTE_MEMBERS AS B
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.