Output Element for WF "Itinerary" Email Campaign

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.

• I could use the rank number, but that would skip the other line of the same rank, and the number of seats would not be right.
• I could use the row number, but then there are multiple lines for each instance where they purchased the same performance more than once.

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