Ticket Limits v2

"[insert flailing air guy here]"

... the correc term for this is "wacky waving inflatable tube man".

https://youtu.be/6GznhT__PXs

In all seriousness... if I'm reading your email correctly... over the course of an entire production season you only want to sell  "X" amount of tickets no matter who the patron is (and as long as they have access to that price type).  if so,  The Pricing Rules tab has a field called "Maximum Seats". In theory, and from my understanding that controls the amount of tickets being sold to the given performance to only "X" seats being sold. Once that limit is reached no more tickets can be sold using that price in the pricing rules.

... of course... someone can prove me wrong and I welcome it for educational purposes.

See? I even got my wacky waving inflatable tube man reference wrong... =,(

Here's my current pricing rule - Am i missing something?  The limit is set to 12 but I've sold 13 tickets.  

Actually, I believe the correct term is 'wacky waving inflatable arm-flailing tube man'. ;)

But in regards to the pricing rule, I was under the impression that a pricing rule only looked at the order and not the performance as a whole. So this would not accomplish what you want. This is an issue that has come up for us many times in the past and to my knowledge there is no out-of-the-box way to do this. When we are requested to do this we encourage the requester not to do it (horrible, I know!) and if they insist then we tell them it has to be manually monitored and is subject to error and then the box office literally manually monitors it until it has reached the capacity and then shuts off the price type. It is super, duper horrible this way and I've been wanting to get a custom procedure in place that would do this for them for a while but other things keep getting in the way.

That said, I know I've seen other people posting about having these automated procedures already in place so if any of them are reading this they just might be willing to share with you!

I haven't fully automated it but have set up a atmated job that checks for a limit and emails box office when it is exceeded - the limit is always set to a number below the actual limit ie set to 80 or 90 when limit is 100

SQL below is checing for price limit globally acros a production season also have written code for when it was on each performace in a prod season but that is a little more complicated

declare @body varchar(max),
           @subject varchar(200),
           @recipients varchar(max),
           @bcc_recipients varchar(200),
           @sold int,
           @price_type int,
           @price_type_name varchar(100),
           @prod_season_no int,
           @prod_name varchar(100),
           @warning int

select   @price_type=<price type id>,
           @prod_season_no=<prod season no>,
           @warning=80, --warning set to a number below the actual limit so that Box Office can decide when to end the price type
           @recipients='boxoffice@theatreroyal.co.uk',
           @bcc_recipients='dba@theatreroyal.co.uk'

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.prod_season_no=@prod_season_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.prod_season_no=@prod_season_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=@prod_season_no

),
@price_type_name=
(

select description
from TR_PRICE_TYPE with (NOLOCK)
where id=@price_type

)


select @body = @prod_name +' has now sold '+convert(varchar(10),@sold)+' tickets at the ' +@price_type_name+' price type',
@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='Tessitura',
@recipients=@recipients,
@blind_copy_recipients=@bcc_recipients,
@subject = @subject,
@body = @body,
@body_format = 'HTML'

end

Hope that helps

Mark

I did a similar thing as Mark is suggesting, but using a code for on-line purchases that prevented any more sales.

However, i think it might be possible to use a list with your Pricing Rule, which needs to be regenerated every n minutes. The list should contain all people who have purchased the max permitted tickets and be used to Exclude those customers.

Might work.

Debbie