Triggered Email SQL - Custom View

It is making sure you only get one line per email address that can make it tricky especially if you have multiple shows in a day etc
The code was designed for running multiple facilities, which we have just stopped doing, and brins in both customer_no and email address so the view alone can be sued for the whole import into wordfly. 

This code enables both Pre and Post shows in one view, as we tend to have few matinees and rarely have different shows at matinee and evening on the same day.
I am using inventory content at normally Production Season level (but code allows it to be added at Performance level just in case for instance the running times are different on matinees or school shows) to bring in Production specific Images and urls as well as running times etc.

Also added checks for whether the customer still has tickets to be printed and/or printed tickets - this allows us to remind the customer to turn up earlier if they have tickets to collect within the email.

We also have specific Performance Keywords set up for both Pre and Post show emails at performance level so we have the option of excluding individual shows from receiving these emails (this is from previous experience where sending a post show with a picture featuring a starring artist who wasn't in that performance can cause problems).

create view [dbo].[lv_pre-post_emails] as
    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() then 'POST-SHOW'
                              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.')),
        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
join TX_INV_TKW ik with (NOLOCK) on i.inv_no=ik.inv_no or p.perf_no=ik.inv_no
join TR_TKW k with (NOLOCK) on ik.tkw=k.id and k.category=8 --TNEW performance category


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

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
(
      (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() then 'POST-SHOW' else 'REMINDER' end,
    convert(varchar(11),p.perf_dt,113),
    DATENAME(weekday,p.perf_dt),
    o.customer_no

I run email/report once a week which shows what information will be used by the emails so that any missing info can be added.

Mark

Here's our View - I can only take credit for tweaking it, as I received it from another organization BUT it's working just fine for us. We look for initiator then the owner of the order and pull in time slot (matinee/evening) as well. You'll see various comments throughout the code.

We have two dynamic lists set up - one for matinee and one for evening performances. We've built custom list query elements to pull in the performance info (from this View) and then suppress people with email restrictions at the list level. We don't currently use contact point purposes or contact permissions, but we'll cross that bridge for v15.1. :) 

CREATE VIEW [dbo].[LV_2ST_PRESHOW]
AS

SELECT DISTINCT
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then ini.customer_no
else a.customer_no
end as 'customer_no',
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then iniea.address
else c.address
end as 'eaddress',
v.description as perf_name,
DATENAME(dw, d.perf_dt) as perf_day,
--d.perf_dt as friendly_perf_dt, -- converting to "friendly" date in Query Elements
format (d.perf_dt, 'h:mm tt') as perf_time,
d.time_slot,
convert(varchar,perf_dt,101) as 'perf_dt', --day without perf time to match with list manager criteria
case
when (
isnull(ini.customer_no,0) > 0 --initiator exists
and isnull(ini.customer_no,0) <> a.customer_no --initiator is different from order owner
and e.cust_type not in (1,8) --order account is not HH/Indv
)
then (case when ini.fname is null then ininame.fname else ini.fname end) --if initiator exists and is a HH, use fname of prim aff of HH
else (case when e.fname is null then g.fname else e.fname end)
end as 'fname',
a.order_no,
d.prod_season_no
FROM dbo.T_ORDER a (nolock)
join T_SUB_LINEITEM b (nolock) on a.order_no = b.order_no
outer apply
(select top 1 address, customer_no
from T_EADDRESS ea (nolock)
where a.customer_no = ea.customer_no
and ea.primary_ind = 'y' and ea.inactive = 'n'
order by last_update_dt desc) as c --get the most recently updated active primary email address on the order owner account
join T_PERF d on d.perf_no = b.perf_no and d.perf_status = 1 --select * from TR_PERF_STATUS
join T_INVENTORY v on v.inv_no = d.perf_no
join dbo.TR_SEASON AS s ON d.season = s.id

--these tables are joined to get order initiator information / order initiator affiliate information (to pull a fname if the order was on a HH record)
left outer join T_CUSTOMER ini on ini.customer_no = a.initiator_no
outer apply
(select top 1 address, customer_no
from T_EADDRESS ead (nolock)
where ini.customer_no = ead.customer_no
and ead.primary_ind = 'y' and ead.inactive = 'n'
order by ead.last_update_dt) as iniea --get the most recently updated active primary email address on the initiator account
left outer join T_AFFILIATION iniaf on iniaf.group_customer_no = ini.customer_no and iniaf.name_ind = -1
left outer join T_CUSTOMER ininame on ininame.customer_no = iniaf.individual_customer_no

--these tables are joined to get order owner affiliate information (to pull a fname if the order was on a HH record)
join T_CUSTOMER e (nolock) on e.customer_no = a.customer_no
left outer join T_AFFILIATION f ON f.group_customer_no = e.customer_no and f.name_ind = -1
left outer join T_CUSTOMER g (nolock) ON g.customer_no = f.individual_customer_no

WHERE (
(isnull(ini.customer_no,0) > 0
and isnull(ini.customer_no,0) <> a.customer_no
and e.cust_type not in (1,8)
and exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = ini.customer_no and primary_ind = 'Y' and inactive = 'N')
) --for cases where there is an initiator, initiator must have email on file

or exists (select top 1 1 from T_EADDRESS (nolock) where customer_no = a.customer_no and primary_ind = 'Y' and inactive = 'N') --otherwise, order owner
) --must have email
AND b.sli_status IN (3, 12) --select * from TR_SLI_STATUS where id in (3,12)

AND (d.perf_dt BETWEEN CONVERT(DateTime, DATEdiff(DAY, -1, getdate())) AND --beginning of tomorrow
DATEADD(SECOND, - 1, CONVERT(DateTime, DATEDIFF(DAY, -2, getdate())))) --end of tomorrow

GO

Thank you all! This is incredibly helpful :)