Triggered Email SQL - Custom View

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

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