Output Element for WF "Itinerary" Email Campaign

Michael Dorsey $organization over 3 years ago

Hello,

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

Heath Wilder $organization over 3 years ago

Hi Michael,

For email output sets We've done a few things over here for email such as wrapping each line of data in an html table to be unpacked by WordFly, or separating the performance days onto separate rows. This view might be of interest

USE [impresario]
GO

/****** Object:  View [dbo].[LV_SDC_SeatAndDates]    Script Date: 2/09/2022 5:44:38 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO








CREATE  VIEW [dbo].[LV_SDC_SeatAndDates]
AS

/*************************************************************************************************
Local View for SDC New Breed 2021 relaunch in WordFly
Using a CTE to set up the seat string using STUFF grouped by order
Part two is STUFFing the seats grouped by Customer (to get dates right for multi bookers)
Assumes only one New Breed perf per order

By:			Heath Wilder
Date:		16 Oct 2021
Summary:	Created to output Seats and Dates as a string for Checking by Patrons

*************************************************************************************************/


WITH SEATSTRING_CTE AS(
SELECT  
	customer_no,
	order_no, --perf_no,
    STUFF(
		( SELECT ', ' + seat_row + ' ' + CAST([seat_num] AS VARCHAR(MAX)   ) + ' ' 
		FROM VX_PERF_SEAT ps
		WHERE (order_no = b.order_no)  
			and seat_status = 8 
			and ps.perf_no in (select perf_no from VS_ELEMENTS_PERF where Season = 196 /*prod_season_no = 1214 962*/)
	FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') 
	,1,2,'') AS Seats, 
	p.perf_dt
FROM VX_PERF_SEAT b 
Join VS_ELEMENTS_PERF p
	on b.perf_no = p.perf_no

where b.perf_no in (select perf_no from VS_ELEMENTS_PERF where Season = 196 /*prod_season_no = 1214 962*/)
and seat_status = 8
and customer_no is not null
--and b.zmap_no = 194

GROUP BY customer_no, order_no, p.perf_dt --, perf_no
)
--Select * from SEATSTRING_CTE

SELECT  
  customer_no, 
    STUFF(( 
	SELECT   Seats + '      ' + CAST(FORMAT([Perf_Dt], 'dddd dd MMM yyyy h:mm tt') AS VARCHAR(MAX)  ) + ' <br /> '  
    FROM SEATSTRING_CTE  
    WHERE (customer_no = a.customer_no)  
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') 
  ,1,0,'') AS Seats 
FROM SEATSTRING_CTE a 

GROUP BY customer_no 


GO