-1

This is a continuation of my question from PHP Sum a value in while loop, but with conditions

I have two tables to be joined, 1 is user and 1 is attendance. column isOt in table attendance is to indicate that the user was granted overtime from superior.

TABLE : attendance
++++++++++++++++++++++++++++++
id   userId   totalHours  isOt  dateRecorded
1    1        0745        0     02-06-2014
2    3        0845        1     07-06-2014
3    1        0945        1     12-06-2014

TABLE : user
+++++++++++++++++++++++
id   name  departmentId
1    John  2
2    Sean  2
3    Allan 2

With the help of many people, solution provided with this query indeed working in helping me to SUM totalHours for each user

But I made some additional query line like below:

$query = "SELECT u.employeeName, u.id, a.isOt, a.dateRecorded,
SUM((CAST(totalHours AS UNSIGNED) % 100)/60 + FLOOR(CAST(totalHours AS UNSIGNED)/100))  grandTotal FROM user u RIGHT JOIN attendance a
ON u.id = a.userId
WHERE u.departmentId = 2
GROUP BY u.id HAVING dateRecorded >= '01-06-2014' <= '31-06-2014'";
$result = mysqli_query($con,$query);
while($row = mysqli_fetch_array($result)) 
{
   print $row['id'] . ' ' . $row['grandTotal'];
}

The above query successfully shows two rows of output like how I wanted But now there's another tricky challenge for beginner like me WHERE

I need to get total overtime by let say 

(if isOt == 1)
{
minus totalHours with 0800 (min working time)
and them sum up the balance and echo it for each user
}

Any help is appreciated, I am still beginner and trying to learn as much I could. Thank you in advance :)

Community
  • 1
  • 1
Ahlan Wasahlan Anta
  • 33
  • 6
  • 1
    Looks to me like you're trying to get people here to write your code for you, bit by bit. Where's __your__ attempt at this new problem? –  Jul 07 '14 at 03:51
  • Hi Mike, i understand you might think I didn't trying But actually i did and stuck within, inside the loop i added the below query to find the sum but not successful $query2 = "SELECT * FROM attendance WHERE userId = $id" $result2 = mysqli_query($con,$query2); while($row2 = mysqli_fetch_array($result2)) $totalOt = 0; { if ($row2['isOt']==1) { $totalOt += $row['totalHours']-'0800'; echo $totalOt; } else { } } – Ahlan Wasahlan Anta Jul 07 '14 at 03:56

1 Answers1

0

I would use a case when statement

SELECT u.employeeName, 
       u.id, 
       a.isOt, 
       a.dateRecorded,
       Case When a.totalHours > 0800 Then 0800 Else totalHours End As NormalHours
       Case When a.totalHours > 0800 Then totalHours - 0800 Else 0 End As OTHours
FROM user u RIGHT JOIN 
     attendance a
ON u.id = a.userId
WHERE u.departmentId = 2
GROUP BY u.id 
HAVING dateRecorded >= '01-06-2014' <= '31-06-2014'

Assuming totalHours is stored in a number format.

Cheruvian
  • 5,628
  • 1
  • 24
  • 34