Hello!
I am trying to get seat locations listed for constituents by performance to put into an output set and am running into an issue when an order has seats in multiple zones. In any of our ticket history tables/views if seats are in different zones they populate as separate rows in the table/view. I feel like I might be close to coming up with a solution, but am a little bit stuck. I'm hoping someone might be able to point me in the right direction!
Here's what I got so far:
SELECT DISTINCT a.customer_no, a.perf_dt, a.prod_season_no, b.order_no, STUFF((SELECT ', ' + (c.seat_row+'-'+c.seat_num) FROM VS_TICKET_HISTORY a WHERE b.order_no = a.order_no FOR XML PATH('')), 1, 1, '') [location]
FROM VS_TICKET_HISTORY a
JOIN T_ORDER_SEAT_HIST b ON a.order_no = b.order_no
JOIN T_SEAT c ON b.seat_no = c.seat_no
I think my from/where query in the STUFF section is not doing what I want it to do. I'm looking to get all the seat row/numbers listed for an order in a given performance. We are planning to send this via email to let subscribers know what dates and seat locations they have for next season, so I'm trying to get this all in one view. Our back up plan is to export it all into excel, combine, and then send, but then we can't make the email triggered for new subscribers.
Does anyone have any suggestions? Any help is appreciated!!
Thanks,
Arik
Arik Beatty (he/him) said:Does anyone have any suggestions? Any help is appreciated!!
Without knowing your data, it's hard to make specific suggestions, but, in general, I would try to avoid views, at least to start, and the fancy "stuff" and "xml path" code, until/unless necessary. Find the data you want first.
E.g. this code is more or less what I use to populate ticket history here, in a single order example. Fast code, with no stuffs or XML , and including Zone, seat location, etc..
Thanks Chris!