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...

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...