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

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