Expired Members Report

Hi,

I am trying to find a query that will make up a report of expired members during a period.

I have a report that shows active members during a period.

What is happening is people that have renewed their membership are showing on both the active and expired report. I think this is because a user that has renewed their membership will have a subscription record for every time that they renew their membership. So the query gets the Active record to show on the active report, but it gets the previous now expired record and shows the user on the expired report.

Here is the query that I am using:

SELECT cb.lastname, cb.firstname, cbp.name, cbs.status, cbs.expiry_date, u.email, cb.cb_phone, cb.cb_address, cb.cb_city, cb.cb_province, cb.cb_postalcode, cb.cb_country FROM #__comprofiler As cb
LEFT JOIN #__users AS u ON u.id=cb.user_id
LEFT JOIN #__cbsubs_subscriptions AS cbs ON cbs.user_id=cb.user_id
LEFT JOIN #__cbsubs_plans AS cbp ON cbs.plan_id=cbp.id
LEFT JOIN #__user_usergroup_map AS ugm ON cb.user_id=ugm.user_id
WHERE ugm.group_id=2 AND cbs.status = 'X' AND cbs.plan_id > 0
AND cbs.expiry_date > '2015-02-01 09:13:35'
AND cbs.expiry_date < '2015-03-11 09:13:39'
ORDER BY cbs.expiry_date DESC, cb.lastname ASC, `cb`.`firstname` asc LIMIT 0, 100

What do I need to do in order to only find the user's latest subscription record and check it instead of checking all of their subscription record. That is how to I find only users that have expired and not renewed?

Thank you for your help on this.

The subscription status you need to check for is 'A' for active. Looks like you're checking for 'X', which is expired.

Hi,

How will checking for Active get me expired members?

Or do you mean change:
cbs.status = 'X'
to
cbs.status != 'A'

Thank you.

Sorry, misunderstood what you're wanting. Thought you wanted active subscriptions, but were getting expired subscriptions. Renewals don't create new subscription rows. They just update the existing subscription row. Only upgrades and new subscriptions will create a subscription row. Sorry, don't understand what you're trying to do. Is this is supposed to be a single row output from CB Query Field or multi-row? If you only want expired subscriptions you query appears to already be setup for that.

Hi Kyle,

Is there a way to get only expired subscriptions and exclude the upgrades?

I have a report that shows all users with an Active subscription and an Expired Subscription report that is supposed to show only users that have not renewed their subscription, ie expired.

But right now the client is confused because there are people on the Active report showing they have an active subscription and they are also on the Expired subscription report showing that their subscription is expired. So, if the user has any type of Active subscription they shouldn't be showing on the Expired report.

How would I need to change my conditions to exclude anyone that has an active subscription?

Hope that makes sense.

Thank you for your help.

Ok, I believe your issue is they're actively subscribed to A, but expired for B. You want them to only show up on your Expired list if they have absolutely no active subscription at all if I'm understanding correctly. For that you need to add a subquery to your query WHERE statement to ensure they don't have any active subscriptions. Example as follows.