Hello friends!
After the wonderful ranking webinars my organization is planning to take full advantage of this functionality and I've customized LP_CUSTOMER_RANK to the point that I would like to apply it to everyone in TEST to see where they fall. The final part of the webinar suggested creating a procedure to run overnight to update T_CUST_RANK based on the same logic as LP_CUSTOMER_RANK but I am having a hard time conceptualizing how best to write this. I've also never wrote a cursor before which I am thinking may be the best option.
Thoughts or ideas?
Hi there - I'm interested in engaging on this topic because I recently made some updates to our ranking system and have been noodling since then on if there are further changes that should be made. We have a job than runs every 2 minutes that checks whether patrons have the correct rank, and if they do not, they are looped through another custom procedure to fix their rank. I love this because it allows donors to be ranked almost immediately and start to get benefits online same-day (within 5 minutes of their gift).
I do agree with Gawain that it doesn't seem efficient to run all records through a rank check overnight, but if you are able to get all your rank logic into LP_CUSTOMER_RANK, maybe an intermediate procedure that checks what a patron's rank IS, and then what it should be, and then executes just those customer_no's through LP_CUSTOMER_RANK?
If you use LP_CUSTOMER_RANK, you shouldn't need a job running every two minutes, as that procedure will run every time a customer record is updated. Thus, any change that might change the customer's rank can be assessed immediately.
We don't run LP_CUSTOMER_RANK every 2 minutes, we run a different procedure to maintain ranks every two minutes!
Right: what I'm saying is that if your rank maintenance logic was in LP_CUSTOMER_RANK you wouldn't need to run anything on a schedule.
Right, that makes sense, but here's the skeleton of how it works over here:
LP_Customer_rank triggers and updates rankings when a customer record is updated (we do have another proc embedded that actually does the ranking updates, then LP_Customer_rank does a bunch of other stuff). This is how the majority of rank updates happen. Our separate, embedded ranking procedure is kept discrete for ease of editing, and so that it can be run solo as a part of...
LUP_Maintain_ranks - runs every 2 minutes and checks to see if patrons have the right rank, and if they don't, runs them through our ranking procedure. This is for folks who need a ranking update, but we haven't touched their record, such as their rank is expiring and should be deleted, or when a patron makes a donation online (On Account), or when we change our benefits levels, etc. Every 2 minutes is probably a bit overkill unless your devo department is getting a lot of complaints from folks donating and wanting to buy discounted tickets right away (which is where I'm betting this schedule cadence came from) or if donations online write directly to a fund, but an overnight job seems reasonable.
I'm sure there are changes that can be made to our flow, but I was just trying to give the OP an example of an organization that is updating T_CUST_RANK via a job (which also seems to be recommended by the webinar and the documentation.) The things we are doing that seem to make this more manageable, that could be implemented solo or together are...
1. separate out the ranking logic by itself, then have LP_customer_rank call this second procedure as part of the customer update trigger. In addition, execute this separate procedure overnight to just update ranks, instead of trying to do all of the other stuff that LP_customer_rank does.
2.Or, create a proc that checks to see if patrons have the correct rank, and if it finds any that do not, will execute LP_customer_rank/your_ranking_procedure to update their rank. This, then, would be scheduled overnight instead.