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?
I advise against. We used to do this, and the nightly procedure was slow and problem-prone (and we only had half a million customers back then). I suppose if you must you might set the cursor to look at a query that is only pulling customers with update dates within a couple of days? But I'm not sure if that column is 100% reliable for all constituent record updates you might want to track.
We do a lot of things in LP_CUSTOMER_RANK, not just setting ranks, and I've pulled out the logic for each operation into a separate stored procedure. These procedures take @customer_no as a parameter (naturally) but are all written to operate on all records if @customer_no = null. Basically you have something like:
update TABLE_NAME
blah blah blah
where ((@customer_no is null) OR (@customer_no = TABLE_NAME.customer_no))
Sadly, that last bit isn't super-efficient for the cases where @customer_no is set, but as long as execution is near to or less than a millisecond it's generally fine for LP_CUSTOMER_RANK, but doing mass table updates instead of customer-at-a-time updates using a cursor can be the difference between seconds and hours.
Thanks for the reply! I am leaning towards not doing a sweep. We have a few different nightly or weekly constituency updates that will run most of who we would want this to run through anyhow. Out of curiosity how long does your full LP_CUSTOMER_RANK procedure take? I am looking at about 20 milliseconds now.