In Loyalty Administrator, you can use Custom SQL with rules and groups to find expired or expiring memberships.

See the following examples of Custom SQL you might want to use.

Members that have expired

SELECT M.membership_id 
FROM FROM #group G INNER JOIN cognetic_members_membership M on M.membership_id = G.membership_id 
WHERE M.membership_expires BETWEEN 01-01-1900 AND GETDATE()

Members that expired in the last 30 days

SELECT M.membership_id 
FROM FROM #group G INNER JOIN cognetic_members_membership M on M.membership_id = G.membership_id 
WHERE M.membership_expires BETWEEN DATEADD(dd, -30, GETDATE()) AND GETDATE()

Members that expired in the last month

SELECT M.membership_id 
FROM FROM #group G INNER JOIN cognetic_members_membership M on M.membership_id = G.membership_id 
WHERE M.membership_expires BETWEEN DATEADD(mm, -1, GETDATE()) AND GETDATE()

Members that will expire in the next 6 weeks

SELECT M.membership_id 
FROM FROM #group G INNER JOIN cognetic_members_membership M on M.membership_id = G.membership_id 
WHERE M.membership_expires BETWEEN GETDATE() AND DATEADD(dd, 42, GETDATE())

Members that have expired or will expire in the next month

SELECT M.membership_id 
FROM FROM #group G INNER JOIN cognetic_members_membership M on M.membership_id = G.membership_id 
WHERE M.membership_expires BETWEEN 01-01-1900 AND DATEADD(mm, 1, GETDATE())

If a renewal recognition has been purchased, the member's expiry date will automatically be changed (based on the settings for the Club).

If the renewal has been processed manually and payment has been received, reset the member's expiry date as follows:

1. In Loyalty Administrator, go to Members and select the member to renew.
2. Select the Process Payment.

The Membership Payment screen is used to process a membership purchase in Loyalty Administrator. The expiry date will be the new expiry date and be based on the old expiry date plus the length of time the membership renewed is for, for example, 1 year.

See also

Membership expiry

Creating a group and rule that finds expired or expiring members