Finding patrons with 10+ tickets to a performance

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_no
from
t_order o
join T_SUB_LINEITEM sl on o.order_no = sl.order_no
join t_perf p on p.perf_no = sl.perf_no
where
p.season = ##
group by customer_no,p.perf_no
having 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_no
join t_perf p on p.perf_no = sl.perf_no
where
p.season = ##
group by customer_no,p.perf_no,o.order_no
having count(sl.li_seq_no) >= 10

Travis

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_no
from
t_order o
join T_SUB_LINEITEM sl on o.order_no = sl.order_no
join t_perf p on p.perf_no = sl.perf_no
where
p.season = ##
group by customer_no,p.perf_no
having 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_no
join t_perf p on p.perf_no = sl.perf_no
where
p.season = ##
group by customer_no,p.perf_no,o.order_no
having count(sl.li_seq_no) >= 10

Travis