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:
100.100.Scenarios:
100 points. They receive a negative behavior that has a -3 value. This would return their totalPoints as 97.2 points which brings them up to 99 for their totalPoints.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.
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