• Replies 2 replies
  • Subscribers 337 subscribers
  • Views 73 views
  • Users 0 members are here
Related

Base Price query

Stuart McLeod
$organization

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.id
group 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_seats
order by th.order_dt, th.order_no

  • $organization

    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.

  • $organization in reply to Jesse Dillman

    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.