MySQL Query Issues with Math not resulting in expected output

Question

Overview:

I built an application that I run locally which allows me to keep track of my kids chores and behaviors that they exhibit on a daily basis. This system has negative and positive behaviors I can assign to them which correspond to a point value on a 100 point scale.

Logic:

• The query only looks at the current day to calculate the points. If ratings were received the day prior, those will not play into their daily total.
• 100 points is the maximum a child can have for the day, even if their ratings cause them to exceed this, it will always return as 100.
• If they don't have any ratings for the day (either positive or negative), it will default their points to the starting point 100.
• When they receive points, their total will adjust accordingly, either going up or down based on the value set for the behavior.

Scenarios:

1. New day without any ratings means the child starts at 100 points. They receive a negative behavior that has a -3 value. This would return their totalPoints as 97.
2. The above child then receives a positive rating worth 2 points which brings them up to 99 for their totalPoints.
3. They receive another positive rating worth 5 points. Since we max out at 100, we would return their totalPoints as 100, regardless of how much it exceeded 100.

Issue:

I built the query and thought everything was working fine but there seems to be a slight math issue with it. When the child received a -3 point rating it brought them to 97 which was expected. I then gave them a positive 4 and it brought their score to 99 instead of 100 like I would have expected.

Query:

 SELECT c.id,
       c.NAME,
       Date_format(From_days(Datediff(CURRENT_DATE, c.age)),
       '%y Years %m Months %d Days')                                 AS age,
       c.photoname,
       c.photonamesmall,
       (SELECT CASE
                 WHEN ( Ifnull(Sum(t.points), (SELECT settingvalue
                                               FROM   settings
                                               WHERE  settingname = 'MaxPoints')
                        ) >= (
                        SELECT
                               settingvalue
                        FROM
                        settings
                        WHERE
                                 settingname = 'MaxPoints') ) THEN 100
                 WHEN ( Sum(t.points) <= 0 ) THEN ( (SELECT settingvalue
                                                     FROM   settings
                                                     WHERE  settingname =
                                                            'MaxPoints')
                                                    + Sum(t.points) )
                 ELSE ( (SELECT settingvalue
                         FROM   settings
                         WHERE  settingname = 'MaxPoints') -
                        Ifnull(Sum(t.points), (SELECT
                        settingvalue
                                               FROM   settings
                                               WHERE
                        settingname = 'MaxPoints')) )
               END
        FROM   behaviorratings AS r
               JOIN behaviortypes AS t
                 ON r.behaviorid = t.behaviortypeid
        WHERE  r.childid = c.id
               AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
       totalPoints,
       (SELECT definitionname
        FROM   behaviordefinitions AS d
        WHERE  totalpoints BETWEEN d.min AND d.max)                  AS
       behaviorRating
FROM   children AS c  

Fiddle:

Here is a link to the SQL fiddle: http://sqlfiddle.com/#!9/fa06c/1/0

The result I expect to see for Child 2 (Brynlee) is 100 not 99.

She started with 100, received a -3 and the received a +4. While I know the math for this order of operations is correct, I need to it to be tweaked to reflect how I expected it to be reflected. 100 - 3 = 97 and then 97 + 4 = 101 (We max out at 100 so 100 would be the totalPoints.

Answer 1

Try this

SELECT c.id,
   c.name,
   DATE_FORMAT(
    FROM_DAYS(
        DATEDIFF(CURRENT_DATE, c.age)
    ),
    '%y Years %m Months %d Days'
  ) AS age,
   c.photoName,
   c.photoNameSmall,
   (SELECT CASE
             WHEN ( Ifnull(Sum(t.points), 0
                    ) + (SELECT settingValue
                                           FROM   settings
                                           WHERE  settingName = 'MaxPoints') >= (
                    SELECT
                           settingValue
                    FROM
                    settings
                    WHERE
                             settingName = 'MaxPoints') ) THEN 100
             WHEN ( Sum(t.points) <= 0 ) THEN ( (SELECT settingValue
                                                FROM   settings
                                                WHERE  settingName =
                                                       'MaxPoints')
                                               + Sum(t.points) )
             ELSE ( (SELECT settingValue
                     FROM   settings
                     WHERE  settingName = 'MaxPoints') -
                    Ifnull(Sum(t.points), (SELECT
                    settingvalue
                                           FROM   settings
                                           WHERE
                    settingName = 'MaxPoints')) )
           END
    FROM   behaviorRatings AS r
           JOIN behaviorTypes AS t
             ON r.behaviorID = t.behaviorTypeID
    WHERE  r.childid = c.id
           AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
   totalPoints,
   (SELECT definitionName
    FROM   behaviorDefinitions AS d
    WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
   behaviorRating
   FROM   children AS c

Basically, using

WHEN ( Ifnull(Sum(t.points), (SELECT settingvalue
                              FROM   settings
                              WHERE  settingname = 'MaxPoints')
)

will only give you 100 when sum(t.points) is null. To get total points you need to do

Ifnull(Sum(t.points), 0) + (SELECT settingvalue
                              FROM   settings
                              WHERE  settingname = 'MaxPoints')

This sql may make it easier to look at

SET @maxPoints := (SELECT settingValue
              FROM   settings
              WHERE  settingName = 'MaxPoints');

SELECT c.id,
   c.name,
   DATE_FORMAT(
    FROM_DAYS(
        DATEDIFF(CURRENT_DATE, c.age)
    ),
    '%y Years %m Months %d Days'
  ) AS age,
   c.photoName,
   c.photoNameSmall,
   (SELECT CASE
             WHEN ( Ifnull(Sum(t.points), 0) + @maxPoints > @maxPoints ) THEN 100
             ELSE ( Ifnull(Sum(t.points), 0) + @maxPoints )
           END
    FROM   behaviorRatings AS r
           JOIN behaviorTypes AS t
             ON r.behaviorID = t.behaviorTypeID
    WHERE  r.childid = c.id
           AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
   totalPoints,
   (SELECT definitionName
    FROM   behaviorDefinitions AS d
    WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
   behaviorRating
   FROM   children AS c

Using 50 as starting point:

SET @maxPoints := (SELECT settingValue
          FROM   settings
          WHERE  settingName = 'MaxPoints');

SET @startingPoint := 50;

SELECT c.id,
c.name,
DATE_FORMAT(
FROM_DAYS(
    DATEDIFF(CURRENT_DATE, c.age)
),
'%y Years %m Months %d Days'
) AS age,
c.photoName,
c.photoNameSmall,
(SELECT CASE
         WHEN ( Ifnull(Sum(t.points), 0) + @startingPoint > @maxPoints ) THEN 100
         ELSE ( Ifnull(Sum(t.points), 0) + @startingPoint )
       END
FROM   behaviorRatings AS r
       JOIN behaviorTypes AS t
         ON r.behaviorID = t.behaviorTypeID
WHERE  r.childid = c.id
       AND Date_format(r.timestamp, '%Y-%m-%d') = Curdate()) AS
totalPoints,
(SELECT definitionName
FROM   behaviorDefinitions AS d
WHERE  totalPoints BETWEEN d.min AND d.max)                  AS
behaviorRating
FROM   children AS c

Sql for applying capping once total points exceeding limitation

SET @maxPoints := (SELECT settingValue
            FROM   settings
            WHERE  settingName = 'MaxPoints');

SET @startingPoint := 50;

SELECT 
    c.id,
    c.name,
    DATE_FORMAT(
    FROM_DAYS(DATEDIFF(CURRENT_DATE, c.age)), '%y Years %m Months %d Days') AS age,
    c.photoName,
    c.photoNameSmall,
    (
        select x.tp 
        from 
        (
            SELECT t.childid,
                @rn:=CASE WHEN @cid <> t.childid THEN 0 ELSE @rn+1 END AS rn,
                @startingPoint + @tp:= CASE 
                    WHEN @cid <> t.childid 
                    THEN ifnull(t.points, 0)
                    ELSE (
                        case when @startingPoint + t.points + @tp > @maxPoints 
                        then @maxPoints - @startingPoint
                        else t.points + @tp end)
                    END AS tp,
                @cid:=t.childid AS clset,
                t.timestamp
            FROM
                (SELECT @tp:= -1) p,
                (SELECT @rn:= -1) n,
                (SELECT @cid:= -1) cd,
                (
                    SELECT r.childid, t.points, r.timestamp
                    FROM behaviorRatings AS r
                    JOIN behaviorTypes AS t ON r.behaviorID = t.behaviorTypeID
                    ORDER BY r.childid, r.timestamp
                ) t
        ) x
        where x.childid = c.id AND Date_format(x.timestamp, '%Y-%m-%d') = Curdate()
        order by x.childid, x.rn desc
        limit 1
    ) AS totalPoints,
    (
        SELECT definitionName
        FROM   behaviorDefinitions AS d
        WHERE  totalPoints BETWEEN d.min AND d.max
    ) AS behaviorRating
FROM   children AS c

Answer 2

Don't make things more complicated than they should be. Choose the right language for your task. In you case it's PHP:

$query = "select settingValue from settings where settingName = 'MaxPoints'";
$result = $this->db->query($query);
$row = $result->fetchAssoc();
$maxPoints = $row['settingValue'];

$query = "select * from children";
$result = $this->db->query($query);
$children = array();
while ($row = $result->fetchAssoc()) {
    $row['totalPoints'] = $maxPoints;
    $children[$row['id']] = $row;
}

$query = "
    select c.id, coalesce(bt.points, 0) as points
    from children c
    join behaviorRatings br on  br.childID = c.id
    join behaviorTypes bt on bt.behaviorTypeID = br.behaviorID
    where date(br.timestamp) = current_date()
    order by c.id, br.timestamp
";

$result = $this->db->query($query);
while ($row = $result->fetchAssoc()) {
    $childId = $row['id'];
    $totalPoints = $children[$row['id']]['totalPoints'];
    $totalPoints = $totalPoints + $row['points'];
    $totalPoints = min($totalPoints, $maxPoints);
    $children[$row['id']]['totalPoints'] = $totalPoints;
}

var_dump($children);

All the logic to get the total points is in the last loop. Now compare it to your query.

However - if you change the rules, allowing to exceed the limit during the day and cut the points only at the end of the day, this could be done in a single query:

select c.*, sub.totalPoints, bd.definitionName
from (
  select c.id, least(100+coalesce(sum(bt.points), 0), mp.settingValue) as totalPoints
  from children c
  join settings mp on settingName = 'MaxPoints'
  left join behaviorRatings br
    on  br.childID = c.id
    and date(br.timestamp) = current_date()
  left join behaviorTypes bt on bt.behaviorTypeID = br.behaviorID
  group by c.id
) sub
join children c on c.id = sub.id
join behaviorDefinitions bd on sub.totalPoints between bd.min and bd.max

http://sqlfiddle.com/#!9/fa06c/71

While it is not a simple query, it is by far not as complex as your try. The accepded solution is doing the same ignoring the rule, that total points have to be cut to 100 every time points are gained (Sum(t.points)).

As I wrote in the comments: To follow that rule, you need some kind of iteration. There is a trick in MySQL using user variables:

select c.id, c.name, sub.totalPoints, bd.definitionName
from (
  select sub.childId, sum(sub.cuttedPoints) + sp.settingValue as totalPoints
  from (
    select 
      @points := coalesce(bt.points,0) as points,
      @lastTotalPoints := case when (c.id = @childId)
        then @totalPoints
        else sp.settingValue
      end lastTotalPoints,
      @totalPoints := least(@lastTotalPoints + @points, mp.settingValue) as totalPoints,
      @totalPoints - @lastTotalPoints as cuttedPoints,
      @childId := c.id as childId
    from children c
    join settings sp on sp.settingName = 'StartPoints'
    join settings mp on mp.settingName = 'MaxPoints'
    left join behaviorRatings br 
      on  br.childID = c.id
      and date(br.timestamp) = current_date()
    left join behaviorTypes bt on bt.behaviorTypeID = br.behaviorID
    cross join (select @childId := null) init_var
    order by c.id, br.timestamp
  ) sub
  join settings sp on sp.settingName = 'StartPoints'
  group by sub.childId
) sub
join children c on c.id = sub.childId
join behaviorDefinitions bd on sub.totalPoints between bd.min and bd.max

Result (Brynlee behaviour: +4 -3 +4 -3):

| id |    name | totalPoints |         definitionName |
|----|---------|-------------|------------------------|
|  2 | Brynlee |          97 | Having an amazing day! |
|  1 |    Maya |         100 | Having an amazing day! |

Brynlee gets 97 points as expected (+4 => 100, -3 => 97, +4 => 100, -3 => 97)

http://sqlfiddle.com/#!9/751c51/28

If you change the new setting "StartPoints" to 50 you will get:

| id |    name | totalPoints |   definitionName |
|----|---------|-------------|------------------|
|  2 | Brynlee |          52 | Not looking good |
|  1 |    Maya |          50 | Not looking good |

Here Brynlee gets 52 points because the limit of 100 was never reached (+4 => 54, -3 => 51, +4 => 55, -3 => 52).

http://sqlfiddle.com/#!9/db020/13

This works because of MySQLs processing order. But this order depends on internal implementation of MySQL. This implementation may be changed in future versions without any warning. I fact - MySQL developers explicitly warn of using user variables that way.

As a general rule, other than in SET statements, you should never assign a value to a user variable and read the value within the same statement. For example, to increment a variable, this is okay:

SET @a = @a + 1;

For other statements, such as SELECT, you might get the results you expect, but this is not guaranteed. In the following statement, you might think that MySQL will evaluate @a first and then do an assignment second:

SELECT @a, @a:=@a+1, ...;

However, the order of evaluation for expressions involving user variables is undefined.

(User-Defined Variables)

I only use "tricks" like that for one-way reports - But never in production code.

So my suggestion is: Change the rules or use a procedural language (PHP).