I am working on creating a proper output set to be used in WordFly for triggered e-mails for performance reminders and thank yous. I've seen some older posts about creating a view to pull the correct data but as a new to SQL user I need a little more guidance than what I've found. Does anyone have experience creating something similar that would be able to help a newbie out? Or even some resource suggestions? Thanks in advance for all of your knowledge sharing!
Best,
Madeline
Hi Madeline,
I have done this for a few organisations now in a couple of different ways. Previous organisations had multiple shows on a week and we wanted to limit them, to only getting 1 email a week so for those I had to use a more dynamic view using a Table Function so that I could pull in all the performances a customer was attending that week.
For my current organisation this is not needed as we had week long runs at one venue and at the other the shows are individual enough that it is unlikely you would get too many emails in a week.
Here is a little explanation of what we are doing just so the logic in the View will make more sense to whoever is looking at it.
Before I came here 4 keywords being applied indicating whether they are eligible for Pre or Post Show emails for Theatre Royal or City hall, which is why I still reference those in theory would have one for Pre and one for Post only reason would keep the 2 is that it gives you more flexibility to switch on and off the emails for the shows, they are also at performance level so that yo can easily stop the emails if a show is cancelled,or if there is a cast change etc and you want to send a manual email instead.
For City Hall pre show emails get sent out the day before, for Theatre Royal 4 days before.In our Post Show Email for City Hall we add the recommendations to Tessitura to pull through, as we can have different shows on Fri-Sun and didn't want someone logging in to manually change those.
Here is the View I currently use.
CREATE view [dbo].[lv_pre-post_emails] as
select theatre=case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' else 'Other' end, email_type=case 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.')), 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' when img.value IS null and f.th_no=8 then 'city-hall.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), --if tickets not printed can display collection details tickets_to_bring=max(case when sli.sli_status in (12) then 'Y' else 'N' end), --If tickets printed can re ind customer they have them to bring with them rec1_perf_url=min(ISNULL(pr1url.value,'')), rec1_perf_name=min(ISNULL(pr1name.description,'')), rec1_perf_text=min(ISNULL(pr1text.value,'')), rec1_perf_img=min(ISNULL(pr1img.value,'generic.jpg')), rec1_perf_dates=min(dbo.LFS_ProdDatesStr(pr1.value)), rec2_perf_url=min(ISNULL(pr2url.value,'')), rec2_perf_name=min(ISNULL(pr2name.description,'')), rec2_perf_text=min(ISNULL(pr2text.value,'')), rec2_perf_img=min(ISNULL(pr2img.value,'generic.jpg')), rec2_perf_dates=min(dbo.LFS_ProdDatesStr(pr2.value)) from t_facility f(NOLOCK) join T_PERF p (NOLOCK) on p.facility_no=f.facil_no and ( (f.th_no = 2 and convert(varchar(10),dateadd(day,4,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --4 days before for TR or (f.th_no = 8 and convert(varchar(10),dateadd(day,1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) --Day before for CH or convert(varchar(10),dateadd(day,-1,GETDATE()),102)=convert(varchar(10),p.perf_dt,102)) 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 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 left join TX_INV_CONTENT pr1 with (NOLOCK) on (i.inv_no=pr1.inv_no or p.perf_no=pr1.inv_no) and pr1.content_type=42 left join TX_INV_CONTENT pr1url with (NOLOCK) on pr1.value=pr1url.inv_no and pr1url.content_type=41 left join t_inventory pr1name with (NOLOCK) on pr1.value=pr1name.inv_no left join TX_INV_CONTENT pr1text with (NOLOCK) on pr1.value=pr1text.inv_no and pr1text.content_type=44 left join TX_INV_CONTENT pr1img with (NOLOCK) on pr1.value=pr1img.inv_no and pr1img.content_type=40 left join TX_INV_CONTENT pr2 with (NOLOCK) on (i.inv_no=pr2.inv_no or p.perf_no=pr2.inv_no) and pr2.content_type=43 left join TX_INV_CONTENT pr2url with (NOLOCK) on pr2.value=pr2url.inv_no and pr2url.content_type=41 left join t_inventory pr2name with (NOLOCK) on pr2.value=pr2name.inv_no left join TX_INV_CONTENT pr2text with (NOLOCK) on pr2.value=pr2text.inv_no and pr2text.content_type=44 left join TX_INV_CONTENT pr2img with (NOLOCK) on pr2.value=pr2img.inv_no and pr2img.content_type=40 join tx_inv_tkw ps with (NOLOCK) on p.perf_no=ps.inv_no and ((ps.tkw in (59,91) and p.perf_dt<GETDATE()) or (ps.tkw in (60,90) and p.perf_dt>GETDATE())) where f.th_no in (2,8)
group by case when f.th_no = 2 then 'Theatre Royal' when f.th_no=8 then 'City Hall' 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
Hope that helps
Mark