Hello,
To value the benefits for our Friends I need to find out how many of them have taken up the free ticket exchange offered. If this happens the fee appears on the constituent record and is then minused off again as a refund. How can I report on this?
Any ideas appreciated.
Thanks, Katie
Hi Kate,
I'm making the assumption that your exchange fees are user defined fees, and that you delete them rather than override them from the order. If you delete them they no longer appear in T_SLI_FEE so that table doesn't help you identify fees anymore, therefore I believe you have to rely on T_ORDER_SEAT_HIST. The SQL below gives you a list of customer numbers and the number of orders they have used which should be a start for your report, hopefully your IT guy can adapt it for your needs...
use Impresario
declare @fee_no int
set @fee_no = 'Put your exchange fee no here'
select o.customer_no,
count(distinct order_no) num_exchanges
from T_ORDER as o with (nolock)
where o.order_no in ( select order_no
from T_ORDER_SEAT_HIST as h with (nolock)
where h.fee_no = @fee_no and
h.event_code = 14 )
group by o.customer_no