Hello,
I had posted this in the Marketing forum, but this might be a good place for it, too.
We're hoping to set up a triggered campaign that will send emails to ticketholders the day before their festival performance(s). In it, we'd like to list the performances they have coming up--the performance name, the date/time, the venue, and the number of tickets.
I have created what I think will work--a custom view that produces the perf data string and lists everything by date/time grouped by the customer, and rank and row number so that I can add multiple elements to the output set and filter each by a rank/row number.
The only problem I have now is some folks placed multiple orders for the same performance.
Can anyone tell me if there's a solution in SQL that will let me put the performance string data together if the performance # is the same for each customer?
Here's the view:
with perf_data as ( select a.customer_no , a.perf_no , a.perf_dt , CONVERT(VARCHAR(10), a.perf_dt, 101) as perf_date , convert(char(5), a.perf_dt, 108) as perf_time , CASE WHEN a.num_seats = 1 THEN ( c.description + ' | ' + CONVERT(VARCHAR(max), a.perf_dt, 100) + ' | ' + a.theater_desc + ' | ' + CAST(a.num_seats as varchar(10)) + ' Ticket' ) ELSE ( c.description + ' | ' + CONVERT(VARCHAR(max), a.perf_dt, 100) + ' | ' + a.theater_desc + ' | ' + CAST(a.num_seats as varchar(10)) + ' Tickets' ) END as perf_str --, rank() over (partition by a.customer_no order by a.perf_dt asc, a.perf_no) [rank] --, ROW_NUMBER() over (partition by a.customer_no order by a.perf_dt asc, a.perf_no) [row_num] from VS_ELEMENTS_TICKET_HISTORY a join TR_SEASON b on a.season = b.id join T_INVENTORY c on a.perf_no = c.inv_no where a.perf_dt >= getdate() and b.type = 2 group by a.perf_no, a.perf_dt, a.customer_no, a.perf_name, c.description, a.theater_desc, a.num_seats ) select b.* , rank() over (partition by b.customer_no, b.perf_date order by b.perf_time asc, b.perf_no) [rank] , ROW_NUMBER() over (partition by b.customer_no, b.perf_date order by b.perf_time asc, b.perf_no) [row_num] from perf_data b
Any insight is always greatly appreciated. Thank you!
Michael
Thanks Gawain! I was thinking I'd open a ticket, but figured I'd come here first in case you had any ideas. Appreciate your input, and all the help you provided getting me this far.
Me again. Sadly, what I set up is not optimized enough for WordFly. As the list of festival goers continues to grow, the list is failing to export to WF. I'm trying everything I can think of, and I've come to the step of making the perf data string into a function and calling that in the view instead of building it there. I've not done custom functions before. I found something to get me started, spliced in my stuff, and it's built, but the problem is it gives the whole string of all of their perfs in one cell. I'm sure it's something simple in the original script that I did not update, but I don't know enough to catch it. Thoughts on what the problem might be?
USE [impresario] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[LFS_TICKET_HISTORY_STRING] ( @customer_no INT) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @perf_str VARCHAR(MAX) SET @perf_str = '' select @perf_str = @perf_str + (ip.description + ' | ' + CONVERT(varchar, p.perf_dt, 100) + ' | ' + t.description + ' | ' + CONVERT(varchar, COUNT(distinct sli.sli_no)) + ' ' + case when COUNT(distinct sli.sli_no) = 1 then 'Ticket' else 'Tickets' end) from T_SUB_LINEITEM as sli inner join T_ORDER as o on o.order_no = sli.order_no inner join T_PERF as p on p.perf_no = sli.perf_no and p.perf_dt >= getdate() inner join TR_SEASON as s on s.id = p.season and s.type = 2 inner join T_FACILITY as f on f.facil_no = p.facility_no inner join TR_THEATER as t on t.id = f.th_no inner join T_INVENTORY as ip on ip.inv_no = p.perf_no WHERE o.customer_no = @customer_no AND o.customer_no = @customer_no group by ip.description, p.perf_dt, t.description, sli.sli_no SET @perf_str = SUBSTRING(@perf_str, 1, 9999) RETURN @perf_str END
PS - I opened a ticket, but no response. This is me trying to do something in the meantime while I wait. Hopefully they pipe up soon, but if this works then their might not be a need? We'll see. Just don't want you to think I ignored your previous suggestion!
The thing collating the results into a single string is this part: "@perf_str = @perf_str + [etc.]". That has the benefit of not needing to know how many performances there are, putting in a few characters to separate them nicely (as well as perhaps something at the end to trim off any extraneous separator characters) might do what you want? Again, it depends on your plans to present the data in Wordfly, but this might be a better strategy. Depending on the number of constituents, however, this isn't going to have super-great performance, maybe worse than the whole row_count thing?
Also, you're not checking against returned SLIs here, so you could be presenting someone with performances that they have returned their tickets to.
As to getting data to Wordfly, I forget, but can you send Extractions to Wordfly? At least within Tessitura, Extractions are going to be much more forgiving in terms of performance.
Extractions with TMS checked can be picked up by WordFly (so long as they are extracted in Tessitura first -- WF just picks up the extracted file). https://support.wordfly.com/hc/en-us/articles/204768404-Import-a-Tessitura-Extraction-list.
Hey all,
Thanks, in no small part, to all of the input on this thread, we have been able to produce a triggered email campaign in WordFly that is sending an itinerary to all festival goers at 8 am the day of their performances. I believe I must've messed up where the ranking/row number was taking place, because once that was put back within the CTE, it started to export. I've also added a custom list criteria so we can get only those with valid tickets (no returns).
We could not have done this without your help. We are extremely pleased with the end result (a board member even gave it a kudos in the last meeting!) and everything is working great.
Thanks again, to all of you! Gawain Lavers, Neil Cole, John A. Moskal II, John Trimble