Unexpected result when joining a table in a year range

Question

I'm working with an users, user_roles and 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.

Expected result

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.

Questions

• 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?

Answer 1

Let me suggest another approach by adding a Years table.

create table p (id int, criterium decimal(18,2), init_year int, end_year int);

create table years(cyear int);

insert into years values(2010),(2011),(2012),(2013),(2014),(2015),(2016),(2017),(2018),(2019),(2020);

insert into p values (1, 93.0, 2018, 2020), (2, 50.0, 2015, 2017), (3, 74.5, 2010, 2012);

Now you can expand dates between initial and ending date, then simply sum year values that match user dates.

select id,
       cyear,
       (criterium / ((end_year - init_year) + 1.0)) as va
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
order by id;

id | cyear |        va
-: | ----: | --------:
 1 |  2018 | 31.000000
 1 |  2019 | 31.000000
 1 |  2020 | 31.000000
 2 |  2017 | 16.666667
 2 |  2015 | 16.666667
 2 |  2016 | 16.666667
 3 |  2010 | 24.833333
 3 |  2011 | 24.833333
 3 |  2012 | 24.833333

db<>fiddle here

select sum((criterium / ((end_year - init_year) + 1.0))) as total
from   p
join   years
on     cyear >= init_year
and    cyear <= end_year
where  cyear >= 2016 and cyear <= 2018;

|     total |
| --------: |
| 64.333333 |

db<>fiddle here