WordFly Triggered Emails - Output Set Elements

Hi Sarah.  I preferred to call it scheduled emails to manage expectations with our marketing team. 

The gotcha was when building tricky output set elements i quickly realised that i wanted a custom view to do the work. Wordfly views and tables (eg: abandoned cart) are pretty good.

Here is the custom view I created for my pre/post show emails

We generally only have one production showing at a time which is why I can get away with this code as even if someone is attending both the matinee and the evening it should show the matinee performance.
This sql is also a little more complicated as for our Schools the reminder is sent out 7 days in advance rather than 4 for other customers.
Using the Content fields for populating things like Production Image and production specific details (warnings etc)
Also check on Ticket status to see if a customer has already received their tickets or if they are waiting to be collected, and include an order number so that they have that if any queries.

select
   theatre=case when f.th_no = 2 then 'Theatre Royal' else 'Other' end,
   email_type=case
                        when convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) then 'TODAY'
                        when p.perf_dt<GETDATE() AND school.key_value is not null then 'SCHOOL-POST-SHOW'
                        when p.perf_dt<GETDATE() then 'POST-SHOW'
                        when school.key_value is not null then 'SCHOOL-REMINDER'
                        else 'REMINDER'
   end,
   o.customer_no,
   weekday=DATENAME(weekday,p.perf_dt) ,
   date=convert(varchar(11),p.perf_dt,113),
   start_time=replace(right(convert(varchar(20),min(p.perf_dt),100),8),':00',''),
   production_name=min(i.description),
   running_time=min(ISNULL(rt.value,'Please check the website closer to the performance.')),
   drinks_allowed=min(ISNULL(da.value,'Yes')),
   warning=min(ISNULL(wa.value,'NA')), -- Adding NA rather than blank/null as guarantee the response write code to say if warning not equal to 'NA'
   header_image=min(case
                                       when img.value IS null and f.th_no=2 then 'theatre-royal.jpg'
                                       else img.value
                                    end ),
   perf_url=min(ISNULL(url.value,'')),
   order_no=min(o.order_no),
   email=MIN(ea.address),
   tickets_to_collect=max(case when sli.sli_status in (3) then 'Y' else 'N' end),
   tickets_to_bring=max(case when sli.sli_status in (12) then 'Y' else 'N' end)
from t_facility f(NOLOCK)
join T_PERF p (NOLOCK) on p.facility_no=f.facil_no
join T_SUB_LINEITEM sli with (NOLOCK) on sli.perf_no=p.perf_no and sli.sli_status in (3,12)
join T_ORDER o with (NOLOCK) on o.order_no=sli.order_no and o.customer_no>0
join T_EADDRESS ea with (NOLOCK) on o.customer_no=ea.customer_no and isnull(ea.primary_ind,'N')='Y' and isnull(ea.inactive,'N')='N'
join t_inventory i with (NOLOCK) on p.prod_season_no=i.inv_no
left join TX_CUST_KEYWORD school with (NOLOCK) on o.customer_no=school.customer_no and school.keyword_no=304
left join TX_INV_CONTENT rt with (NOLOCK) on (i.inv_no=rt.inv_no or p.perf_no=rt.inv_no) and rt.content_type=37 --running times
left join TX_INV_CONTENT da with (NOLOCK) on (i.inv_no=da.inv_no or p.perf_no=da.inv_no) and da.content_type=38 --drinks allowed
left join TX_INV_CONTENT wa with (NOLOCK) on (i.inv_no=wa.inv_no or p.perf_no=wa.inv_no) and wa.content_type=39 --warnings
left join TX_INV_CONTENT img with (NOLOCK) on (i.inv_no=img.inv_no or p.perf_no=img.inv_no) and img.content_type=40 --header image file
left join TX_INV_CONTENT url with (NOLOCK) on (i.inv_no=url.inv_no or p.perf_no=url.inv_no) and url.content_type=41 --url bit after www.theatreroyal.co.uk/.../ ie young-frankenstein
join tx_inv_tkw ps with (NOLOCK) on p.perf_no=ps.inv_no and ((ps.tkw in (59) and p.perf_dt<GETDATE()) or (ps.tkw in (60) and p.perf_dt>GETDATE())) --postshow keywords for earlier perfs and preshow for dates ahead

where f.th_no in (2)
and
(
   (f.th_no = 2 and school.key_value is not null and convert(varchar(10),dateadd(day,7,GETDATE()),102)=convert(varchar(10),p.perf_dt,102) ) --7 days before for TR schools
   or
   (f.th_no = 2 and school.key_value is null AND convert(varchar(10),dateadd(day,4,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --4 days before for TR
   or
   convert(varchar(10),dateadd(day,-1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102) -- Yesterday for post show
   or
   convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) -- Today
)

group by case when f.th_no = 2 then 'Theatre Royal' else 'Other' end,
case when convert(varchar(10),GETDATE(),102)=convert(varchar(10),p.perf_dt,102) then 'TODAY' when p.perf_dt<GETDATE() AND school.key_value is not null then 'SCHOOL-POST-SHOW' when p.perf_dt<GETDATE() then 'POST-SHOW' when school.key_value is not null then 'SCHOOL-REMINDER' else 'REMINDER' end,
convert(varchar(11),p.perf_dt,113),
DATENAME(weekday,p.perf_dt),
o.customer_no

Hope that helps

Mark