Creating LIVE Alerts (Re: Today's coffee, and no, not in Analytics)

Hi folks,

This morning someone asked about being able to have Tessitura Analytics send off an email based on a threshold. The request involved information being live, and TA can’t do that at this time due to cube refresh times. HOWEVER, I asked our Audience Services Director Luke Robertson about doing this for an event a while back, and he had a non-Analytics way we used.

declare @body varchar(max),
           @subject varchar(200),
           @recipients varchar(max),
           @bcc_recipients varchar(200),
           @sold int,
           @price_type int,
           @price_type_name varchar(100),
           @perf_no int,
		   @perf_dt datetime,
           @prod_name varchar(100),
           @warning int
​
select   @price_type = 420, -- a4a is 299, 393 is compweb
           @perf_no = 12565,
           @warning=4, --warning set to a number below the actual limit so that Box Office can decide when to end the price type
           @recipients='boxoffice@yourorg.org',
           @bcc_recipients=''
​
if exists (
           select COUNT(*) 
           from T_SUB_LINEITEM sli with (NOLOCK) 
           join T_PERF p with (NOLOCK) on sli.perf_no=p.perf_no and p.perf_no=@perf_no
           where sli.price_type=@price_type 
           and sli.sli_status in(2,3,12)
           having COUNT(*)>= @warning
)
begin
​
select 
​
@sold=(
select COUNT(*) 
from T_SUB_LINEITEM sli with (NOLOCK) 
join T_PERF p with (NOLOCK) on sli.perf_no=p.perf_no and p.perf_no=@perf_no
where sli.price_type=@price_type 
and sli.sli_status in(2,3,12)
),
​
@prod_name=
(
select description 
from T_INVENTORY with (NOLOCK) 
where inv_no=@perf_no
),
​
@price_type_name=
(
select description 
from TR_PRICE_TYPE with (NOLOCK) 
where id=@price_type
),
​
@perf_dt=
(
select p.perf_dt
from T_PERF p with (NOLOCK)
where p.perf_no = @perf_no
)
​
​
select @body = @prod_name + ' performance on ' + convert(varchar(20),@perf_dt) +' has now sold '+convert(varchar(10),@sold)+' tickets at the ' +@price_type_name+' price type! Turn them off!',
@subject=@prod_name +' has now sold '+convert(varchar(10),@sold)+' tickets at the ' +@price_type_name+' price type.'
​
​
exec msdb.dbo.sp_send_dbmail 
@profile_name='PRCS',
@recipients=@recipients,
@blind_copy_recipients=@bcc_recipients,
@subject = @subject,
@body = @body,
@body_format = 'HTML'
​
end

This script runs once per hour, and fires off an email only if it finds that a threshold has been reached. Disclaimer: I didn’t write this. Luke didn’t write this. We aren’t sure who did; but it is something that can be manipulated to do what was requested today in a more “live” fashion.

Below is more info from Luke!

I wish I could remember where exactly I got it from, but I adapted it from a script someone shared on one of the semi-official Tess network related dev groups. This specific example is looking for just a single price type, but it would be pretty easy to modify to look for a total ticket count, or multiple price types instead. This is also looking at just a single performance (a class) and could be modified to look at an entire production season or subset of performances.

I haven't had time to try this yet, but someone once suggested that instead of firing off an email, you build a price event that will shut off or change whatever you want it to, but schedule the event for like ten years from now. Then a script like this instead of just sending an email could also update the date on that price event and move it from ten years from now to today and effectively automatically shut off or change whatever wants changing.

Can I help any further with this? Uh, I’m going to say no, not right now. I just know this exists! But possibly someone else can help.

Happy alerting!
Jack Ridenour-Starnes (he/him)
Development Manager
Portland Center Stage

PS If you wrote this, many thanks!!!