LP_UPDATE_SUB_HISTORY Nightly Job

Hi. An option here would be to create your own scheduled job.  You might try the SQL below where N equals your Lapsed Sub constituency number from tr_constituency, X is the season number for 07-08 found in tr_season and Y and Z are equal to the season numbers for 08-09 and 09-10.

insert tx_const_cust (constituency, customer_no)
select N, customer_no from v_cust_pkg
where season = X and pkg_type = 1
and customer_no not in (select customer_no from v_cust_pkg where season in (Y,Z) and pkg_type =1)

You might wish to double check tr_pkg_type to confirm pkg_type 1 = full season. 

If you are still selling 09-10 subscriptions, you might schedule the first step of your scheduled SQL job to clear out the existing Lapsed Sub constituencies (delete tx_const_cust where constituency = N) before reloading.

Hope that helps,

Warren

Shakespeare Theatre

Probably the easiest thing to do is use the Manage Constituency utility in the application.  If you make a dynamic list which selects the constituents who only have tickets in the 07-08 season; use that list with the utility scheduled to run just after the subs history procedure you should get the results you want. 

Best,

Anna

Anna E. Wessely

Manager Asia Pacific/Senior Application Specialist

Tessitura Network, Inc

+1 888 643 5778 x 308

awessely@tessituranetwork.com

www.tessituranetwork.com

Hi Revanth,

These stored procedures you are running, are they custom or the standard ones Tessitura provides? Send me your stored procedures and I'll take a look at them. I'm certain there has to be away to do this.

Naomi

I just finished this it was mainly for TSTATS so that we could use the Is subscriber and Is single ticket buyer attributes .  We have a Lapsed Subscriber, Current subscriber, lapsed single ticket buyer and current single ticket buyer constituencies.  What I did was have a second step that runs after the LP_UPDATE_HISTORY. 

The procedure checks the susb and ticket history tables respectively.  Then applies the constituencies.  I can provide the procedures if you would like.

I have an overnight job that does this - it runs AFTER the LT_SUB_HIST table nightly job has run.  Note that the code below is in a single script that allocates various constituencies - in the order:

1.    Full subscriptions for the current year

2.    Co-subscriber for the current year

3.    Full subscriptions for the next year ie NEW subscribers

4.    3 Pack subscribers

5.    Lapsed subscribers

6.    Single Ticket buyer

--LAPSED SUBSCRIBERS

--remove lapsed subscriber constituency

       delete from TX_CONST_CUST where constituency = 51

--assign LAP constituency for subscribers lapsed in the previous 3 years

       insert tx_const_cust (constituency, customer_no)

       select distinct 51, customer_no from lt_sub_hist sh

       join t_pkg p on p.pkg_no=sh.pkg_no and p.pkg_type=1

       where p.season in

(select id from vrs_season where fyear between datepart(yyyy,getdate())-3 and datepart(yyyy,getdate())-1 and description like 'ACO%')

and customer_no not in (select customer_no from tx_const_cust where constituency in (5,227,228))

Notes:

• Each constituent will only have one of the above constituencies based on the hierarchy in the list at top
• We have a single subscription season per calendar year
• 51 is the ID of the LAP(sed) constituency
• Only packages of type 1 (Full) are considered
• Because of our membership of a consortium, all our seasons are prefixed with our company acronym
• The fyear value in the season is used rather than specifying the year as I want this SP to keep rolling from year to year without having to nominate a year parameter

Should you have any questions, please feel free to contact me directly.

Martin