• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 64 views
  • Users 0 members are here
Related

LP_UPDATE_SUB_HISTORY Nightly Job

Revanth Anne
$organization

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.

 

Parents
  • $organization

     

    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

     

     

Reply
  • $organization

     

    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

     

     

Children
No Data