I’m trying to find the monthly paying members where 30 or more days have elapsed since their last payment. Also, I’m trying to find the yearly paying members where 365 or more days have elapsed since their last payment. Can someone see the problem with my logic?
Thanks!
SELECT
memberID,
frequency,
datediff(now(), max(payDate)) as daysSincePayment
CASE
when frequency = 'Monthly' then 30
when frequency = 'Yearly' then 365
END as monthlyORyearly
FROM
payments
WHERE
memberID in (select memberID from members where subscription != 'Free')
and memberID not in (717, 8192)
GROUP BY
memberID
HAVING
daysSincePayment > monthlyORyearly;
[quote=“molona, post:3, topic:192636, full:true”]
As painful as it is, many databases will not let you use the alias of a field in a condition, in this case your HAVING clause.
[/quote]i think you’re thinking of the WHERE clause
as far as i recall, aliases in HAVING are fine everywhere
Thank you WolfShade! Once again you have been very helpful in pointing out what I overlooked.
However, now that this query is returning results, I’m noticing that they are not correct. The payments table records all transactions from Paypal. Some of my members may start out paying for my Monthly Silver membership, then they might upgrade to the Monthly Gold. Still others may start out on a Yearly gold and then later downgrade to a monthly gold.
The query below is indeed finding the max(p.payDate) of a member’s payment, but it isn’t necessarily getting the correct frequency (Monthly/Yearly) associated with the row. What do I need to do to make that happen?
Thanks!
SELECT
date_format(min(p.payDate), '%m-%d-%Y') as firstPayment,
date_format(max(p.payDate), '%m-%d-%Y') as lastPayment,
datediff(now(), max(p.payDate)) as daysSincePayment,
p.frequency,
m.memberID,
m.firstName,
m.lastName,
m.subscription,
m.country,
CASE
when p.frequency = 'Monthly' then 45
when p.frequency = 'Yearly' then 380
END as days2wait
FROM
payments p
INNER JOIN
members m
ON
p.memberID = m.memberID
WHERE
m.subscription != 'Free'
and p.memberID not in (717, 8192)
GROUP BY
memberID
HAVING
daysSincePayment > days2wait
ORDER BY
daysSincePayment desc;
[quote=“busboy, post:6, topic:192636, full:true”]The query below is indeed finding the max(p.payDate) of a member’s payment, but it isn’t necessarily getting the correct frequency (Monthly/Yearly) associated with the row. What do I need to do to make that happen?[/quote]what you need is to understand ~why~ you’re getting the wrong values
Thanks for helping my r937. I read both links that you sent, in an attempt to learn how to pull this off. Then I modified my query. It runs without an error, but still doesn’t give the right results. For members who started on the monthly plan, and then just recently upgraded to the yearly plan, their corresponding row in the results is still showing "Monthly’ instead of ‘Yearly’. So my complex join is still not giving the max(payDate) and frequency.
Thoughts?
SELECT
p1.paymentID,
date_format(min(p1.payDate), '%m-%d-%Y') as firstPayment,
date_format(max(p1.payDate), '%m-%d-%Y') as lastPayment,
datediff(now(), max(p1.payDate)) as daysSincePayment,
p1.frequency,
m.memberID,
m.firstName,
m.lastName,
m.subscription,
m.country,
CASE
when p1.frequency = 'Monthly' then 45
when p1.frequency = 'Yearly' then 380
END as days2wait
FROM
payments p1
LEFT JOIN
payments p2
ON
p1.paymentID = p2.paymentID
AND p1.frequency = p2.frequency
AND p1.payDate < p2.payDate
INNER JOIN
members m
ON
p1.memberID = m.memberID
WHERE
m.subscription != 'Free'
and p1.memberID not in (717, 8192)
GROUP BY
memberID
HAVING
daysSincePayment > days2wait
ORDER BY
daysSincePayment desc;
[quote=“busboy, post:11, topic:192636, full:true”]
Thanks for helping my r937. I read both links that you sent, in an attempt to learn how to pull this off. Then I modified my query. [/quote]
where’s the subquery?
SELECT m.memberID
, m.firstName
, m.lastName
, m.subscription
, m.country
, p.paymentID
, DATE_FORMAT(x.min_payDate,'%m-%d-%Y') AS firstPayment
, DATE_FORMAT(x.max_payDate,'%m-%d-%Y') AS lastPayment
, p.frequency
, CASE WHEN p.frequency = 'Monthly' THEN 45
WHEN p.frequency = 'Yearly' THEN 380
END AS days2wait
FROM members AS m
LEFT OUTER
JOIN ( SELECT memberID
, MIN(paydate) AS min_paydate
, MAX(paydate) AS max_paydate
, DATEDIFF(CURRENT_DATE,MAX(payDate)) AS daysSincePayment
FROM payments
GROUP
BY memberID ) AS x
ON x.memberID = m.memberID
LEFT OUTER
JOIN payments AS p
ON p.memberID = x.memberID
AND p.paydate = x.max_paydate
WHERE m.subscription <> 'Free'
AND m.memberID NOT IN (717, 8192)
AND x.daysSincePayment >
CASE WHEN p.frequency = 'Monthly' THEN 45
WHEN p.frequency = 'Yearly' THEN 380
END
ORDER
BY x.daysSincePayment DESC
We’re getting close, but it’s still not quite right. I pulled up the details of one member and her most recent payment was a Yearly Gold and yet the results from this query show it as Yearly Silver. This is because this member several months ago was on the Silver membership but has since upgraded.
Also, the daysSincePayment that you added to the x query was not showing up in the results so I added:
datediff(x.max_payDate, x.min_payDate) AS difference
Here is query as it is now. Any idea why the most recent payment is not correctly showing that it’s a Yearly Gold?
Thanks!
SELECT m.memberID
, concat(m.firstName,' ', m.lastName) AS fullName
, m.subscription
, DATE_FORMAT(m.lastLogin, '%m-%d-%Y') AS lastLogin
, p.paymentID
, DATE_FORMAT(x.min_payDate,'%m-%d-%Y') AS firstPayment
, DATE_FORMAT(x.max_payDate,'%m-%d-%Y') AS lastPayment
, datediff(x.max_payDate, x.min_payDate) AS difference
, p.frequency
, CASE WHEN p.frequency = 'Monthly' THEN 45
WHEN p.frequency = 'Yearly' THEN 380
END AS days2wait
FROM members AS m
LEFT OUTER
JOIN ( SELECT memberID
, MIN(paydate) AS min_paydate
, MAX(paydate) AS max_paydate
, DATEDIFF(CURRENT_DATE,MAX(payDate)) AS daysSincePayment
FROM payments
GROUP
BY memberID ) AS x
ON x.memberID = m.memberID
LEFT OUTER
JOIN payments AS p
ON p.memberID = x.memberID
AND p.paydate = x.max_paydate
WHERE m.subscription <> 'Free'
AND m.memberID NOT IN (717, 8192)
AND x.daysSincePayment >
CASE WHEN p.frequency = 'Monthly' THEN 45
WHEN p.frequency = 'Yearly' THEN 380
END
ORDER
BY difference DESC
The p.type column will always have a value of either ‘Gold’ or ‘Silver’. The p.frequency column will always be either ‘Monthly’ or ‘Yearly’. The m.subscription column is the ultimate place to indicate if they are either a ‘Free’, ‘Silver’ or ‘Gold’ member, despite whatever payments they may have made in the past.