We are in the process of rezoning. I need a query that will list all seat numbers of all unpaid and unreserved E holds not assigned to any patron number, grouped by Performance. I will need to run this query every day so we can rezone the price for those specific seats.
Eventually, I'd like a report that our BO Manager can run through Tessitura each day.
I appreciate your sql-scripting assistance! Thanks!
Thanks! I’ll give it a shot! J
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Rachel Schlaff Sent: Friday, July 10, 2015 2:09 PM To: Tiffany Evans Subject: Re: [Tessitura Technical Forum] SQL Query help
Hi Tiffany:
Start by modifying your join condition when you join to T_PROD_SEASON; make it
ON T_PERF.prod_season_no = T_PROD_SEASON.prod_season_no
This should slim down your results considerably.
Once that's done, you'll still have some extra rows because each distinct address/phone combination will return an additional row for every sublineitem. If you want only one row per sublineitem, you should be able to modify your query to find the best address and best phone number in the constituent account.
Hope it helps!
From: Tiffany Evans <bounce-tiffanyevans7088@tessituranetwork.com> Sent: 7/10/2015 10:20:31 AM
Hi,
I'm so close to having this query work, but I need a fresh set of eyes to tell me what I'm doing wrong! I'm trying to pull 3 FY of ticketing data and include customer data as well as transactional data and 'genre' which we don't keep in Tessitura. I was provided a script but i had to add the customer data and genre assignment. Sadly, I'm now getting well over 5 million rows and Im positive it has to do with how I have my joins set up! Can someone please help!?
Here's the original Query:
-- ENTER THE FISCAL YEAR YOU WANT TO GO BACK TO SET @StartYear = 2013 -- Return perf data SELECT T_ORDER.customer_no, 'Tessitura' system, T_ORDER.order_no, rtrim(T_PERF.perf_code) perf_code, rtrim(T_INVENTORY.description) perf_name, convert(CHAR(19), T_PERF.perf_dt, 120) perf_date, convert(CHAR(19), T_ORDER.order_dt, 120) order_dt, rtrim(TR_SEASON_TYPE.description) season_type, CASE T_SUB_LINEITEM.sli_status WHEN 13 THEN sl.paid_amt ELSE T_SUB_LINEITEM.paid_amt END paid_amt, rtrim(T_ZONE.short_desc) price_zone, rtrim(TR_SECTION.description) section, t_seat.seat_row row, t_seat.seat_num seat, rtrim(TR_PRICE_TYPE.description) price_type, rtrim(T_FACILITY.description) venue, rtrim(ltrim(TR_SEASON.description)) season, 1 ticket_type, T_SUB_LINEITEM.pkg_no, rtrim(T_PKG.description) pkg_name, rtrim(TR_SLI_STATUS.description) sli_status, rtrim(TR_MOS.description) mos, CASE WHEN T_SUB_LINEITEM.price_type = 2 THEN 'COMP' WHEN T_ORDER.MOS = 19 THEN 'GROUP' WHEN T_SUB_LINEITEM.sli_status = 13 THEN 'DON' WHEN T_SUB_LINEITEM.pkg_no = 0 THEN 'STB' ELSE T_PKG.description END purchase_type FROM T_ORDER (NOLOCK) INNER JOIN T_LINEITEM (NOLOCK) ON T_ORDER.order_no = T_LINEITEM.order_no INNER JOIN T_SUB_LINEITEM (NOLOCK) ON T_LINEITEM.li_seq_no = T_SUB_LINEITEM.li_seq_no INNER JOIN T_PERF ON T_LINEITEM.perf_no = T_PERF.perf_no INNER JOIN T_FACILITY ON T_PERF.Facility_no = T_Facility.Facil_no INNER JOIN TR_SEASON ON T_Perf.season = TR_SEASON.id INNER JOIN T_ZONE ON T_sub_LINEITEM.zone_no = T_ZONE.zone_no and t_perf.zmap_no = t_zone.zmap_no INNER JOIN TR_PRICE_TYPE ON T_SUB_LINEITEM.price_type = TR_PRICE_TYPE.id INNER JOIN TR_SEASON_TYPE ON TR_SEASON.type = TR_SEASON_TYPE.id INNER JOIN T_INVENTORY ON T_INVENTORY.inv_no = T_Perf.perf_no LEFT JOIN T_SEAT (NOLOCK) ON T_sub_lineitem.seat_no = t_seat.seat_no and t_seat.is_seat=1 LEFT JOIN TR_SECTION ON T_SEAT.section = TR_SECTION.id LEFT JOIN T_PKG (NOLOCK) ON T_SUB_LINEITEM.pkg_no = T_PKG.pkg_no LEFT JOIN TR_SLI_STATUS ON T_SUB_LINEITEM.sli_status = TR_SLI_STATUS.id LEFT JOIN TR_MOS ON T_ORDER.MOS = TR_MOS.id LEFT JOIN T_SUB_LINEITEM sl (NOLOCK) ON t_sub_lineitem.ret_parent_sli_no= sl.sli_no WHERE T_Sub_lineitem.perf_no > 0 AND T_Lineitem.primary_ind = 'Y' --Include Rank#1 choice only AND T_Sub_lineitem.due_amt = T_Sub_lineitem.paid_amt --Include only items that are paid in full AND T_Sub_lineitem.sli_status in (3,6,12,13) --"Seated, Paid", "Unseated paid", "Ticketed, Paid", "Return in Benevolent Mode" AND tr_season.fyear >= @StartYear AND TR_SEASON.type = 1;
__________________________________________________***///////**************** Here's what I edited and is now giving me 5 million rows and is taking forever to run. I tried indexing a table, but I feel like i'm missing something basic, like using the wrong joins for some reason! HALP! :(
Use impresario DECLARE @StartYear int -- ENTER THE FISCAL YEAR YOU WANT TO GO BACK TOO SET @StartYear = 2013 -- Return perf data SELECT T_ORDER.order_no, T_ORDER.customer_no, T_CUSTOMER.sort_name sort_name, T_CUSTOMER.lname last_name, T_CUSTOMER.fname Name, T_ADDRESS.street1 Address1, T_ADDRESS.street2 Address2, T_ADDRESS.street3 Company, T_ADDRESS.city city, T_ADDRESS.state STATE, T_ADDRESS.postal_code ZIP, T_CUSTOMER.email email, T_PHONE.phone phone, rtrim(T_PERF.perf_code) perf_code, rtrim(T_INVENTORY.description) perf_name, convert(CHAR(19), T_PERF.perf_dt, 120) perf_date, convert(CHAR(19), T_ORDER.order_dt, 120) order_dt, rtrim(TR_SEASON_TYPE.description) season_type, CASE T_SUB_LINEITEM.sli_status WHEN 13 THEN sl.paid_amt ELSE T_SUB_LINEITEM.paid_amt END paid_amt, rtrim(T_ZONE.short_desc) price_zone, rtrim(TR_SECTION.description) section, t_seat.seat_row row, t_seat.seat_num seat, rtrim(TR_PRICE_TYPE.description) price_type, rtrim(T_FACILITY.description) venue, rtrim(ltrim(TR_SEASON.description)) season, 1 ticket_type, T_SUB_LINEITEM.pkg_no, rtrim(T_PKG.description) pkg_name, rtrim(TR_SLI_STATUS.description) sli_status, rtrim(TR_MOS.description) mos, CASE WHEN T_SUB_LINEITEM.price_type = 2 THEN 'COMP' WHEN T_ORDER.MOS = 19 THEN 'GROUP' WHEN T_SUB_LINEITEM.sli_status = 13 THEN 'DON' WHEN T_SUB_LINEITEM.pkg_no = 0 THEN 'STB' ELSE T_PKG.description END purchase_type, CASE WHEN T_PROD_SEASON.prod_season_no IN (17372,17554,11929,11676,15235,11926,11923,13699,11923,11926,13699,15235,15198,13499,15198) THEN 'COMEDY' WHEN T_PROD_SEASON.prod_season_no IN (11829,15354,15160,11756,15529,17452,15275,15123,17411,11792,11866,12138,15313,15529,13495,13611,13536,15313) THEN 'DRAMA' WHEN T_PROD_SEASON.prod_season_no IN (1533) THEN 'FLEX' WHEN T_PROD_SEASON.prod_season_no IN (17183,14780,13458,17484) THEN 'MUSICAL' WHEN T_PROD_SEASON.prod_season_no IN (13663,17200,15275,17557,17640,13401,13663,15275) THEN 'NEW PLAY' END FROM T_ORDER (NOLOCK) INNER JOIN T_CUSTOMER ON T_CUSTOMER.customer_no = T_ORDER.customer_no INNER JOIN T_ADDRESS (NOLOCK) ON T_CUSTOMER.customer_no = T_ADDRESS.customer_no INNER JOIN T_PHONE ON T_ADDRESS.customer_no = T_PHONE.customer_no INNER JOIN T_LINEITEM (NOLOCK) ON T_ORDER.order_no = T_LINEITEM.order_no INNER JOIN T_SUB_LINEITEM (NOLOCK) ON T_LINEITEM.li_seq_no = T_SUB_LINEITEM.li_seq_no INNER JOIN T_PERF ON T_LINEITEM.perf_no = T_PERF.perf_no INNER JOIN T_FACILITY ON T_PERF.Facility_no = T_Facility.Facil_no INNER JOIN TR_SEASON ON T_Perf.season = TR_SEASON.id INNER Join T_PROD_SEASON ON TR_SEASON.id = T_PROD_SEASON.season INNER JOIN T_ZONE ON T_sub_LINEITEM.zone_no = T_ZONE.zone_no and t_perf.zmap_no = t_zone.zmap_no INNER JOIN TR_PRICE_TYPE ON T_SUB_LINEITEM.price_type = TR_PRICE_TYPE.id INNER JOIN TR_SEASON_TYPE ON TR_SEASON.type = TR_SEASON_TYPE.id INNER JOIN T_INVENTORY ON T_INVENTORY.inv_no = T_Perf.perf_no LEFT JOIN T_SEAT (NOLOCK) ON T_sub_lineitem.seat_no = t_seat.seat_no and t_seat.is_seat=1 LEFT JOIN TR_SECTION ON T_SEAT.section = TR_SECTION.id LEFT JOIN T_PKG (NOLOCK) ON T_SUB_LINEITEM.pkg_no = T_PKG.pkg_no LEFT JOIN TR_SLI_STATUS ON T_SUB_LINEITEM.sli_status = TR_SLI_STATUS.id LEFT JOIN TR_MOS ON T_ORDER.MOS = TR_MOS.id LEFT JOIN T_SUB_LINEITEM sl (NOLOCK) ON t_sub_lineitem.ret_parent_sli_no= sl.sli_no WHERE T_Sub_lineitem.perf_no > 0 AND T_SUB_LINEITEM.order_no = sl.order_no AND T_Lineitem.primary_ind = 'Y' --Include Rank#1 choice only AND T_Sub_lineitem.due_amt = T_Sub_lineitem.paid_amt --Include only items that are paid in full AND T_Sub_lineitem.sli_status in (3,6,12,13) --"Seated, Paid", "Unseated paid", "Ticketed, Paid", "Return in Benevolent Mode" AND tr_season.fyear >= @StartYear AND TR_SEASON.type = 1 AND T_ADDRESS.primary_ind = 'Y'
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!