I'm probably missing something that is really, really simple, but I can't for the life of me figure out what it is that I'm not doing correctly...
I have this query which is used to pull out hours people have completed in volunteering and then assign them an award based on the amount of hours submitted. Not difficult...
The nested IF solution is horrible and was only a fallback to see if it was just the CASE that was messing up. Turns out, the janky nested IF solution works perfectly, where as my CASE solution is still broken.
The query is only run once annually to pull off final results, so performance isn't really a problem (the nested IF query currently has an execution time of 0.0095 seconds / 700 rows which is perfectly adequate), it's more of the fact that it is thoroughly annoying my that it's not working and want to understand why for future reference.
For reference the hour values are stored as
DECIMAL(8,2), subsequently the value of total_hours is also of the same type.
The output I'm looking for is:
| id | first_name | last_name | total_hours | award | |----|------------|------------|-------------|----------| | 1 | Bob | Harrington | 0.50 | Silver | | 2 | Jim | Halpert | 800.00 | Platinum | | 3 | Dwight | Shrute | 130.00 | Gold | | 4 | Michael | Scott | 5.00 | Bronze |
The CASE statement results in all rows having the value of 'Less than 1 hour' for
award, EXCEPT those where total_hours equals 1.00, in which the value of
award equals 'Bronze'.
The nested IF statements result in the table being generated correctly, as per the example above.
Here is my current CASE query, that doesn't work:
SELECT m.id, m.first_name, m.last_name, total_hours, CASE total_hours WHEN total_hours >= 1 <= 50 THEN 'Bronze' WHEN total_hours >= 51 <= 125 THEN 'Silver' WHEN total_hours >= 126 <= 249 THEN 'Gold' WHEN total_hours >= 250 THEN 'Platinum' ELSE 'Less than 1 hour' END AS award FROM ( SELECT member_id, sum(hours) total_hours FROM volunteering_hours WHERE authorise_date > 0 AND validate_date > 0 AND delete_date = 0 GROUP BY member_id ) hour_query LEFT JOIN members m ON m.id = member_id ORDER BY total_hours DESC
What I've tried so far:
- Placing the raw comparison numeric values in quotes.
- Giving the comparison numeric values decimal places.
- Trying the CASE statement with only one comparison, just as a test, that being;
WHEN total_hours > 1 THEN 'GT 1' ELSE 'LT 1' END award, all columns where still coming up as
LT 1after running the query - meaning it's failed.
- Grouping the CASE statement
- Changing the syntax of each range comparison to
total_hours >= 1 && total_hours <= 50, etc.. and it still yielded the same failed result
My current nested IF solution which looks horrible, but at least is working, is:
SELECT m.id, m.first_name, m.last_name, total_hours, IF(total_hours >= 1 && total_hours <= 50, 'Bronze', IF(total_hours >= 51 && total_hours <= 125, 'Silver', IF(total_hours >= 126 && total_hours <= 249, 'Gold', IF(total_hours >= 250, 'Platinum', 'Less than 1 hour') ) ) ) award FROM ( SELECT member_id, sum(hours) total_hours FROM volunteering_hours WHERE authorise_date > 0 AND validate_date > 0 AND delete_date = 0 GROUP BY member_id ) hour_query LEFT JOIN members m ON m.id = member_id ORDER BY total_hours DESC
Can someone please shower me in some knowledge as to why the CASE isn't working?
Thanks in advance. :)