I'm working with an
point_standards. Users need to achieve a certain points in a certain period based on which role they have. Points are retrieved from courses which are done in a specific year. Retrieving the user points is not the problem.
For the certain points a user has to achieve, a criteria is defined. These are made for 3 years and can not overlap. See the example below.
This means, from 2018 to 2020, the user with the role that is assigned to these criterias needs to achieve 93 points.
In 2018, the user should achieve 31 points (93 / 3).
The problem is when the user wants to check the criteria for years corresponding to more criteria. Lets say the user wants to check the criteria in the years 2018, 2017 and 2016. This means:
(93 / 3) + (50 / 3) + (50 / 3)
When ceiling these sums, this should result into a criteria of 65 points for 2018, 2017 and 2016.
For my expected end result, I want to get an overview with all users and the criterias for specific years.
In the example below, I'm trying to retrieve the criteria for a single user for the years 2018, 2017 and 2016.
SELECT `users`.first_name, ps.points FROM `users` # Join the user roles table INNER JOIN `user_roles` ON `users`.`role_id` = `user_roles`.`id` # Get the criteria for 2018, 2017 and 2016 LEFT JOIN (SELECT id, role_id, COALESCE(points / 3) AS points FROM point_standards WHERE 2018 BETWEEN YEAR(start_year) AND YEAR(end_year) OR 2017 BETWEEN YEAR(start_year) AND YEAR(end_year) OR 2016 BETWEEN YEAR(start_year) AND YEAR(end_year)) ps ON (user_roles.id = ps.role_id) WHERE users.id = 123 GROUP BY `users`.`id`, ps.id ORDER BY `points_internal` DESC
This results in 2 rows with the correct criteria.
In this case, it should return 3 rows. This because of I want to see the criteria of the years 2018, 2017 and 2016.
I then hoped I could sum the
points of these 3 rows for each user.
- Why is it only returning 2 rows instead of 3? I've selecte 3 years to look in.
- How can I sum these amount of rows together so I can get an overview of all users?