Output Element for WF "Itinerary" Email Campaign

I think you need to use a similar concept of using something (a derived table, another CTE, temp table, table variable) to aggregate the number of tickets grouped by customer_no before you rank() and ROW_NUMBER() in your final Select statement. The final Select statement should get the data from your aggregated data. Does that make sense?

Neil Cole, Gawain Lavers, and John Trimble,

Thank you for your responses. 

I got your email with more detail, Neil, but I was a little intimidated by the suggestion as my SQL skills are intermediate at best. I decided to look into improving the view with Gawain's and John's input first, because it seemed easier to change the tables being referenced to get current data. Then, I planned to use Neil's tips to improve the view even further; however, I found that by simply changing the tables in the view, I was able to get the desired result (number of seats grouped by perf). Spot checking seems to indicate that this would work. Could I be missing something that would bite me later on?

Here's the new code:

with perf_data as (

select

a.customer_no
, b.perf_no
, c.perf_dt
, CONVERT(VARCHAR(10), c.perf_dt, 101) as perf_date
, convert(char(5), c.perf_dt, 108) as perf_time
, d.description as perf_name
--, e.description as venue
, f.description as venue
, count(b.sli_no) as num_seats
, CASE
	WHEN count(b.sli_no) = 1
	THEN 
	(
	d.description 
	+ ' | ' 
	+ CONVERT(VARCHAR(max), c.perf_dt, 100) 
	+ ' | ' 
	+ f.description
	+ ' | ' 
	+ CAST(count(b.sli_no) as varchar(10))
	+ ' Ticket'
	)
	ELSE
	(
	d.description 
	+ ' | ' 
	+ CONVERT(VARCHAR(max), c.perf_dt, 100) 
	+ ' | ' 
	+ f.description 
	+ ' | ' 
	+ CAST(count(b.sli_no) as varchar(10))
	+ ' Tickets'
	)
  END as perf_str

from T_ORDER a

left join T_SUB_LINEITEM b
on a.order_no = b.order_no

left join T_PERF c
on b.perf_no = c.perf_no

left join T_INVENTORY d
on c.perf_no = d.inv_no

left join T_FACILITY e
on c.facility_no = e.facil_no

left join TR_THEATER f
on e.th_no = f.id

left join TR_SEASON g
on c.season = g.id

where c.perf_dt >= getdate()
and g.type = 2
and a.order_dt >= '2023-05-11'

group by a.customer_no, b.perf_no, c.perf_dt, d.description, e.description, f.description, 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 

I appreciate all of you for your willingness to help.

Michael

Hi Michael,

One major caveat about using T_SUB_LINEITEM: not all of the items are vended tickets.  I reflexively remove SLIs with status ids of 4, 7, 8 and 13 as these all represent returns.  I think the rest are mostly safe?  These will pick up unseated orders as well, in case that matters.

I think for clarity you generally want to do your grouping in one place and then tack on the "details" elsewhere.

Lastly, I'm not entirely certain what you're doing with the ranking and row numbers now.  Did you want to explicitly denote an ordering based on performance date for use elsewhere?  Anyway, here's how I'd attack the rest of it:

declare @min_perf_dt datetime,
	@min_order_dt datetime,
	@season_type int
;

select @min_perf_dt = CURRENT_TIMESTAMP,
	@min_order_dt = CONVERT(datetime, '2023/01/01'),
	@season_type = 4 --CP FA
;

with perfs_ordered as (
	select
		o.customer_no,
		sli.perf_no,
		COUNT(distinct o.order_no) as orders, --just for funsies
		COUNT(distinct sli.sli_no) as num_seats
	from T_SUB_LINEITEM as sli
		inner join T_ORDER as o on o.order_no = sli.order_no
			and o.order_dt >= @min_order_dt
		inner join T_PERF as p on p.perf_no = sli.perf_no
			and p.perf_dt >= @min_perf_dt
		inner join TR_SEASON as s on s.id = p.season
			and s.type = @season_type
	where
		sli.sli_status not in (4,7,8,13)
	group by
		o.customer_no,
		sli.perf_no
), perf_detail as (
	select
		po.customer_no,
		dbo.FS_GET_CONSTITUENT_DISPLAY_NAME(po.customer_no) as customer,
		po.perf_no,
		p.perf_dt,
		CONVERT(varchar, p.perf_dt, 100) as perf_date,
		CONVERT(varchar, p.perf_dt, 108) as perf_time,
		ip.description as perf_name,
		t.description as venue,
		po.num_seats,
		ip.description + ' | '
			+ CONVERT(varchar, p.perf_dt, 100) + ' '
			+ CONVERT(varchar, p.perf_dt, 108) + ' | '
			+ t.description + ' | '
			+ CONVERT(varchar, po.num_seats) + ' '
			+ case when po.num_seats = 1 then 'Ticket' else 'Tickets' end
		as perf_str

	from perfs_ordered as po
		inner join T_PERF as p on p.perf_no = po.perf_no
		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
)
--select * from perfs_ordered where orders > 1
select * from perf_detail order by customer_no, perf_no
;

Gawain - this is truly above and beyond. Thank you for taking the time on this! I expected to have to feel my way through it, but this saves me a lot of guesswork and potentially overlooked pitfalls, i.e., returned SLI's -- that should've been obvious!

To answer your question about ranking and row number, we're trying to produce an itinerary email that lists all of the performances and the associated information (date/time, venue, number of seats). WordFly doesn't let you have output set elements that produce multiple rows, so the idea is to use the rank/row number as a parameter on the output element. Put a bunch of the output elements on the output set, and assign each one a parameter of the rank or row number so that it spits out their list of performances. Then assign each element in the set to a data field in WF. I think this will work? Never done anything like it---which is why, again, I really appreciate you help.

Thanks again,

Michael

Can't speak for sure to "safe" at any given org, but here's the seat status list

Part of my decision there seems to stem from the statuses we never seem to have appear.  Over the last two years these have been the only ones used:

sli_status description slis
1 Unseated, Unpaid 199
2 Seated, Unpaid 2386
3 Seated, Paid 114630
4 Return 44491
6 Unseated, Paid 8366
7 Void-Returned in this order 33988
8 Void-Returned in other order 11739
12 Ticketed, Paid 558755
13 Return in Benevolent Mode 1395

We have the same list as Gawain Lavers except that we no longer use the stock "Return in Benevolent Mode" functionality.  We just return the tickets normally and put the money on a donation because that is how our Development and Marketing departments wanted to do it.

We have never used temporary seating, and I am not sure why else a seating would fail if the facility is set up properly.

This will work if you are willing to cut it off at an arbitrary point, e.g. "Here are your next 10 performances!", since you'll have to have a different output set element for every count of performance you're planning on displaying.

For something like this you'll definitely want to use ROW_NUMBER.  ROW_NUMBER guarantees distinct numbers, where RANK can (theoretically) have multiple rows with the same rank.  That probably wouldn't happen here, but no reason to risk it.

Now, there are ways to take something like this and concatenate it into a single row: this is done for the default constituencies constituent header element.

For the ROW_NUMBER option, something like this:

declare @min_perf_dt datetime,
	@min_order_dt datetime,
	@season_type int
;

select @min_perf_dt = CURRENT_TIMESTAMP,
	@min_order_dt = CONVERT(datetime, '2023/01/01'),
	@season_type = 4 --CP FA
;

with perfs_ordered as (
	select
		o.customer_no,
		sli.perf_no,
		COUNT(distinct o.order_no) as orders, --just for funsies
		COUNT(distinct sli.sli_no) as num_seats
	from T_SUB_LINEITEM as sli
		inner join T_ORDER as o on o.order_no = sli.order_no
			and o.order_dt >= @min_order_dt
		inner join T_PERF as p on p.perf_no = sli.perf_no
			and p.perf_dt >= @min_perf_dt
		inner join TR_SEASON as s on s.id = p.season
			and s.type = @season_type
	where
		sli.sli_status not in (4,7,8,13)
	group by
		o.customer_no,
		sli.perf_no
), perf_detail as (
	select
		po.customer_no,
		dbo.FS_GET_CONSTITUENT_DISPLAY_NAME(po.customer_no) as customer,
		po.perf_no,
		p.perf_dt,
		CONVERT(varchar, p.perf_dt, 100) as perf_date,
		CONVERT(varchar, p.perf_dt, 108) as perf_time,
		ip.description as perf_name,
		t.description as venue,
		po.num_seats,
		ip.description + ' | '
			+ CONVERT(varchar, p.perf_dt, 100) + ' '
			+ CONVERT(varchar, p.perf_dt, 108) + ' | '
			+ t.description + ' | '
			+ CONVERT(varchar, po.num_seats) + ' '
			+ case when po.num_seats = 1 then 'Ticket' else 'Tickets' end
		as perf_str

	from perfs_ordered as po
		inner join T_PERF as p on p.perf_no = po.perf_no
		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
), perf_detail_numbered as (
	select
		ROW_NUMBER() over (partition by pd.customer_no order by pd.perf_dt, pd.perf_no asc) as perf_order,
		pd.*
	from perf_detail as pd
)
--select * from perfs_ordered where orders > 1
--select * from perf_detail order by customer_no, perf_no
select * from perf_detail_numbered order by customer_no, perf_order
;

What I've done on occasion is ask what reports the requester uses most, then match the suppressions in the new thing to those in the old thing without really giving a lot of thought to externalities like how many of each status we might have or something we used to do but don't any more.  Admittedly, this is kind of lazy on my part, but if I go wholly in on reviewing statuses, then I have to review them everywhere...

Thanks again for your help with this. It's working great for our purposes. The only thing is, List Mgr can't export it to CSV, and WordFly can get it but it takes a while to import. Any tips or tricks to make it go a little faster?

Huh, that's odd.  Tessitura doesn't give you the option, or it fails?  What about using Execute an Output Set?

It tries for a long time, then fails with a timeout error message. I haven't tried execute an output set, but will try that now.

Is the output set generation in the client also very slow?  I assume you've basically got that view set up with 10 or so different output set elements to pull in multiple performances.  Anything with OVER is going to be be generally slow, output sets have a bunch of overhead on top of that, and you'll be running it multiple times...it's not particularly avoidable, mind you.  I'd go ahead and put in a ticket about the timeout when trying to save a CSV, there might be something hosting services can do about the timeout.

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.

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

Michael