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.

Hi,

How will checking for Active get me expired members?

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

Thank you.

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.