Hi all,
I'm trying to add Base Price to a query that pulls on Ticket History. Does someone have a SQL query to find that? This one below doesn't seem to be pulling it correctly:
select top 1000 th.perf_no, th.perf_name, th.perf_dt, th.customer_no, th.order_no, th.order_dt, th.zone_no, Price_Type = pt.description, th.tck_amt, th.num_seats, Base_Price = sum(gp.price)from T_TICKET_HISTORY th (nolock) left outer join T_PERF_PRICE_TYPE ppt (nolock) on th.price_type = ppt.price_type and th.perf_no = ppt.perf_no left outer JOIN [dbo].ft_get_prices(null) gp ON ppt.id = gp.perf_price_type and th.zone_no = gp.zone_no -- and ppt1.base_ind = 'Y' left outer join TR_PRICE_TYPE pt (nolock) on th.price_type = pt.idgroup by th.type, th.perf_no, th.perf_name, th.perf_dt, th.customer_no, th.order_no, th.order_dt, th.zone_no, pt.description, th.tck_amt, th.num_seatsorder by th.order_dt, th.order_no
Have you tried FT_GET_HOME_PRICES? From what I can tell, the FT_GET_PRICES function takes a date argument, and if no date is provided it uses prices from pricing events as of the current date. But FT_GET_HOME_PRICES I think uses the original prices as if you had clicked the HOME icon on that shows pricing table. I tested it out on one of our shows with pricing events and from what I can tell it did return original prices.
Hi Jesse, sorry, I went on vacation just as you responded. I ended up using these joins:
Select th.perf_no, th.perf_name, th.perf_dt, th.customer_no, th.order_no, th.order_dt, th.zone_no, th.tck_amt, th.num_seats,
Base_Price = sum(pp.start_price)
from T_TICKET_HISTORY th (nolock) left outer join T_PERF_PRICE pp (nolock) on th.perf_no = pp.perf_no and th.zone_no = pp.zone_no left outer join T_PERF_PRICE_TYPE ppt (nolock) on pp.perf_no = ppt.perf_no and pp.perf_price_type = ppt.id and ppt.base_ind = 'Y'
But comparing it to the FT_GET_HOME_PRICES function, I get the exact same prices so it looks like I'm doing something right! Thanks for your reply which gives me a little confidence in the prices I'm pulling.