Hi ,
Its end of the season I am working on modifying the nightly procedures.
However I have got a request to add a new constituency named 'Laped Sub' for the subscriber of 07-08 and not a subscriber of 08-09 and 09-10. But this stored procedure will just add in to the Constituencies( previous year,future year and current subscriber or any combinations accordingly).
'Laped Sub'should stand unique,is there anyway I could accomplish this.
please let me know if you have any ideas , anything would be really helpful.
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_pkgwhere season = X and pkg_type = 1and 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