Does anyone know how to reactivate an inactive membership? We have run into this issue trying to use the Membership Extend Expiry utility...but some of our memberships we need to extend are already inactive...
If you make a list of the folks you want this applied to and have ssms access you can run something like the following (you'll def want to change the dates below, this is just a quick example).
--UPDATE cm --uncomment to update--SET cm.expr_dt='2021-08-01 00:00:00' --uncomment to updateSELECT cm.customer_no, cm.memb_level, '2021-08-01 00:00:00' AS new_expr, cm.expr_dt AS old_expr --comment out when updatingFROM dbo.TX_CUST_MEMBERSHIP cmJOIN dbo.T_LIST_CONTENTS l ON l.customer_no = cm.customer_no WHERE l.list_no=24887 --insert your list of identified candidates here AND cm.memb_level IN ('SUP','PAR','FAM','ADV','SPN','AMB') --insert your levels hereAND cm.expr_dt >= '2020-03-01 00:00:00' AND cm.expr_dt <= '2021-04-01 00:00:00' --expirations between march 1 2020 and midnight apr 1 2020 AND cm.current_status IN (1,7) --inactive, lapsed
To run this, we'd need:
the list of candidates,
the dates to which we want the new expiration date changed,
the abbreviations of the membership levels.
Then the query updates the TX_CUST_Membership table with the new expiration dates, is that right? Would that be enough to re-activate? Is activation looking solely at expiration dates?
And the range of expiration dates you want to update. Probably easiest to run this with fixed dates for each month unless you want to plug in some relative dates. I thiiiink the membership update utility updates the status, but I could be making that up. I'll try to get a chance to test it. We're still waiting to re-open to update our memberships.
Thanks Amanda, this worked brilliantly to get the dates right. I am still a little stuck on how to get them back to being 'Active'. They now have an expiry date in the future, but with an inactive membership. I tried running the normal update utility, but It seems to look for Active or Pending memberships, and not the reverse. So if you or anyone has any brilliant ideas, that would be awesome.
Could it be as simple as something like this...?--UPDATE cm --uncomment to update--SET cm.current_status='2' --uncomment to updateSELECT cm.customer_no, cm.memb_level, cm.current_status, '2' AS new_status, cm.current_status AS old_status --comment out when updatingFROM dbo.TX_CUST_MEMBERSHIP cmJOIN dbo.T_LIST_CONTENTS l ON l.customer_no = cm.customer_noWHERE l.list_no=1393 --insert your list of identified candidates hereAND cm.expr_dt >= '2020-07-01 00:00:00' AND cm.expr_dt <= '2020-07-31 00:00:00' --expirations between march 1 2020 and midnight apr 1 2020AND cm.current_status IN (1,7) --inactive, lapsed
Exactly! You may want to bump second expiration to... AND cm.expr_dt < '2020-08-01 00:00:00' to catch any that don't end at midnight. I think the default end time is like 23:59:59.997
May also want to check on their constituency end dates if you use those.
Also, realizing you need to mark the record as current...so that would be: cm.cur_record='Y' after making it active again.
Ah yes. Probably good to compare one that's untouched and one of these in tx_cust_membership to make sure all the columns look good.