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 TOSET @StartYear = 2013-- Return perf dataSELECT 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_typeFROM 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_noWHERE 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 impresarioDECLARE @StartYear int-- ENTER THE FISCAL YEAR YOU WANT TO GO BACK TOOSET @StartYear = 2013-- Return perf dataSELECT 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_noWHERE 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'
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!