We want to look at the following:
The list of constituents who have placed 3 or more orders over the past two seasons, buying at a selected Price Type.
We've run into problems where the list generated doesn't contain the right results, which must mean we're selecting the wrong criteria in list builder. Or perhaps two of the criteria are referencing different tables?
Has anyone else done any analysis by orders rather than performances booked? Any tips / help / solutions greatly appreciated.
thanksAlison AtkinsonLondon Philharmonic Orchestra
Here's an example of how to do this using the manual SQL edit option in List builder. This example builds a list of those customers having 2 or more orders when looking at fiscal year 2015 and 2016 for tickets sold under the TR_PRICE_TYPE.ID value of 18 (Sub Extra here). You may need to further filter the seasons you want to look at by adding a Season ID to the below query instead of what I use below with the Fiscal Years as a FYear value should be populated for each TR_SEASON.ID.
You can paste this directly in List Builder using the "manual Edit" option...just supply your own Price Type value and change the seasons you want to include in your search.
HTH
-doug
selectdistinct o.customer_no
from t_sub_lineitem li
join t_lineitem l on li.li_seq_no = l.li_seq_no and l.primary_ind = 'Y'
join t_perf p on li.perf_no = p.perf_no
join t_order o on li.order_no = o.order_no
join TR_PRICE_TYPE pt on li.price_type = pt.id
JOIN TR_SEASON s on p.season = s.id
JOIN T_CUSTOMER c on o.customer_no = c.customer_no
where c.inactive = 1
and li.sli_status in (2,3,6,12) --select * from tr_sli_status
and s.fyear in (2015,2016)
and o.customer_no > 0
and pt.id = 18 --Your Price Type Here
group by o.customer_no
having count(distinct o.order_no) >= 3
From: Alison Atkinson <bounce-alisonatkinson5345@tessituranetwork.com> Sent: 8/6/2015 12:48:24 PM
thanks Alison Atkinson London Philharmonic Orchestra