Has anyone figured out a way to see what shows guests exchange out of and then change into each season? It can be aggregate but I need to be able to see the leaving from X show going into Y show and then totals for the year.
- Chris
Not sure if this helps as a non-analytics starting point, but here is some SQL I was provided with (from Martin Keen I think).
We use to see what people exchange their gift vouchers into.
select
i.description prod_season_desc,
p.perf_code,
pt.description price_type,
convert(varchar,osh.event_date,103) return_dt,
count(distinct osh.seat_no) num_seats,
sum(osh.amount) val_seats
from T_ORDER_SEAT_HIST osh
join T_PERF p
on p.perf_no=osh.perf_no
join T_INVENTORY i
on i.inv_no = p.prod_season_no --although you could join on p.perf_no if you want the performance rather than the production season name
join TR_PRICE_TYPE pt
on pt.id=osh.price_type
where osh.event_code = 3
--and p.prod_season_no = XXX --use this if it's a whole production season
--and p.perf_code = '<sample perf_code>' --use this if it's just one performance
group by i.description, p.perf_code, pt.description, convert(varchar,osh.event_date,103)
Hey Ronan. This is some great code. Now I just need to figure out how to change the date structure to appear in US format (month, day, year vs day, month, year). Thanks for this bit of code!
Hopefully, this is useful: https://www.mssqltips.com/sqlservertip/1145/date-and-time-conversions-using-sql-server/