CODEWITHSUNDEEP
mysql
Yamaha32088
Yamaha32088

Reputation: 4163

getting greatest value in specific column and row

I have a table that is going to have several time stamp entries added throughout the day with a specific employee ID tied to each entry. I am curious how I would get the first timestamp of the day and the last time stamp of the day to calculate amount of time worked for that specific employee on the specific date. My table is below:

+----+------------+----------+---------+---------------------+-----------+------------+-----------+---------+
| id | employeeID | date     | timeIn  | jobDescription      | equipType | unitNumber | unitHours | timeOut |
+----+------------+----------+---------+---------------------+-----------+------------+-----------+---------+
|  1 |          1 | 01/13/13 | 8:17 pm | Worked in Hubbard   | Dozer     | 2D         |     11931 | 8:17 pm |
|  2 |          1 | 01/13/13 | 8:17 pm | Worked in Jefferson | Excavator | 01E        |      8341 | 8:18 pm |
+----+------------+----------+---------+---------------------+-----------+------------+-----------+---------+

so far I have a query like this to retrieve the time values:

$stmt = $conn->prepare('SELECT * FROM `timeRecords` WHERE `date`= :dateToday AND `employeeID` = :employeeID ORDER BY employeeID ASC');
    $stmt->execute(array(':employeeID' => $_SESSION['employeeID'], ':dateToday' => $dateToday));

But I am unsure of how to obtain the greatest value in the timeOut column

Upvotes: 1

Views: 88

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

Really, you just need the aggregate MAX() and MIN() grouped by employeeID. Use the TIMEDIFF() function to calculate the difference in time between the two.

SELECT 
  `employeeID`,
  MIN(`timeIn`) AS `timeIn`,
  MAX(`timeOut`) AS `timeOut`,
  TIMEDIFF(MAX(`timeOut`), MIN(`timeIn`)) AS `totalTime`
FROM `timeRecords`
WHERE
  `date` = :dateToday
  AND `employeeID` = :employeeID
/* Selecting only one employeeID you don't actually need the GROUP BY */
GROUP BY `employeeID`

However, this won't report the total time worked if an employee clocks in and out several times during one day. In that case, you would need to SUM() the result of the TIMEDIFF() for each of the in/out pairs.

Something like:

SELECT
  `employeeID`,
  /* Assumes no times overlap across rows */
  SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(`timeOut`, `timeIn`)))) AS `totalTime`
FROM `timeRecords`
WHERE
  `date` = :dateToday
  AND `employeeID` = :employeeID
GROUP BY `employeeID`

Upvotes: 1

Related Questions