Hi all,
I need to export certain bits of information on our Subscription Orders. The ticket import/export utility does not run on subscription packages, so I'm hoping to write a script to gather this information (only the most basic info - price type, price, name, perf date/time/name, and seat loc). I'm wondering if anyone has ever done this before or if there is a Tess canned report that does gather at least some of this info for output to an Excel? The other fly in the ointment is that we only use custom/flex packages - no fixed seat locs per customer. Formatting the locs so that they are in one line per customer on the Excel is the part I'm most concerned about..
Thanks!
Frannie
Hey Fran,
Here is a quick snippet you should be able to use. This is just off the top of my head so you might need to fix it a bit:
SELECT cust.fname,
cust.lname,
ord.order_no,
tpt.description AS price_type,
tp.perf_dt,
inv.description AS production,
ts.seat_num,
ts.seat_row
FROM t_customer cust
JOIN t_order ord ON cust.customer_no = ord.customer_no
JOIN T_SUB_LINEITEM tsl ON ord.order_no = tsl.order_no
JOIN T_PERF tp ON tsl.perf_no = tp.perf_no
JOIN T_SEAT ts ON tsl.seat_no = ts.seat_no
JOIN TR_PRICE_TYPE tpt ON tsl.price_type = tpt.id
JOIN T_PROD_SEASON tps ON tp.prod_season_no = tps.prod_season_no
JOIN T_INVENTORY inv ON tps.prod_season_no = inv.inv_no
WHERE tps.season = 108 AND ISNULL(tsl.pkg_no, 0) <> 0
Replacing the season # with your own obviously. That will give you one row per seat however if I read it correctly, you want them grouped so the seat locations are in one field, right? You can do that with a cursor to loop over the above results and start appending the seat locations with a separator OR you can use this sweet custom aggregate function that you can use like any other aggregate (SUM, COUNT, etc) but the results will be a CSV of whatever field you put it it, so it would be something like this…
dbo.MAKE_CSV(ts.seat_row + TS.seat_num)
GROUP BY
cust.fname,
tpt.description,
inv.description
There are two caveats to that custom aggregate function. One it doesn’t guarantee any kind of sorting as SQL decides how to order aggregates internally and second it is written in C# as a CLR object. In order to use it you’ll need to import the DLL into your database and register the function within SQL. That sounds a lot harder than it is and I can help you with that if you want to go that route. But in any case, I use that thing all the time so it may be worth getting because it saves me tons of time on reports and such.
If the ordering of those seats is important, I can help you with a loop that will do what you need. Hope that helps!
Sean
spinto@ctgla.org
213.972.7292
Feel free to call or email me directly if you need