Trying to use CASE results in conjunction with the having clause

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;

Put a comma at the end of this line, as you are using the CASE as part of the SELECT.

V/r,

:slight_smile:

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=“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

[quote=“WolfShade, post:2, topic:192636, full:true”]Put a comma at the end of this line…[/quote]FALSE (apologies if you don’t know Dwight Schrute)

comma goes in front of next line

:slight_smile:

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;

Is this Oracle, or MS-SQL Server? I ask because I’ve had issues with Oracle JOINs and WHERE clauses that work in MS-SQL but don’t work in Oracle.

If it’s Oracle, try moving your WHERE clause parameters to your ON clause.

If it’s MS-SQL, I’m not sure.

Also, the above may or may not apply, as I’ve never used INNER JOIN, before.

HTH,

:slight_smile:

It’s not Oracle or MS-SQL, rather, it’s mySQL.

[quote=“WolfShade, post:7, topic:192636, full:true”]
Is this Oracle, or MS-SQL Server? [/quote]
this can only be mysql

compare the non-aggregate columns in the SELECT clause with the GROUP BY clause

all other database systems besides mysql would flag that as an error

[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

please read http://dev.mysql.com/doc/refman/5.0/en/group-by-handling.html

as for how to fix this issue, you can use a subquery for the max row, then join it back to the main table

see http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html

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

which column distinguishes gold from silver?

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.

Thanks brother!

okay, so add p.type to the SELECT clause

since it comes off the last payment row, it’ll be accurate

I’m good to go now. Thanks r937!

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.