Hey all, My group sales manager wants me to pull a list of all our patrons who have purchased 10+ tickets during a given season for a single show. I've clicked around my list maker and extraction manager and couldn't figure out a good method. Does anyone have a preferred way to get this data? Thanks!
Use the maunal edit in the list manager, change the ## to the season number in question and this will return people you have 10 more seats, for a perf, within the season. You may need to filter out some other things too, but this little query is in its simplest form and gives you the base to build on.
select distinct o.customer_nofrom t_order o join T_SUB_LINEITEM sl on o.order_no = sl.order_nojoin t_perf p on p.perf_no = sl.perf_nowhere p.season = ##group by customer_no,p.perf_nohaving count(sl.li_seq_no) >=10
If you want to check it in SSMS, this has the seat count, performance number and order number to go and peek at the accounts.
select distinct o.customer_no,o.order_no,p.perf_no ,count(sl.li_seq_no)from t_order o join T_SUB_LINEITEM sl on o.order_no = sl.order_nojoin t_perf p on p.perf_no = sl.perf_nowhere p.season = ##group by customer_no,p.perf_no,o.order_nohaving count(sl.li_seq_no) >= 10
Travis