Create form with custom layout Topic is solved

[nadir]

Hello Kevin,

The new logic works fine. Thanks !. When no "check out" entry exists, The TimeOut defaults to "17:00:00" and the comments are set to "Not checked out". But the hours worked are being set to 0 because there is no "check out" entry.

[kev1n]

Try this updated query:

Code: Select all

$select = "
SELECT
  e.employee_id AS employee_id,
  e.full_name AS Name,
  FROM_UNIXTIME(MIN(a.`timestamp`), '%H:%i:%s') AS TimeIn,
  IF(
    MAX(CASE WHEN a.`type` = 'check out' THEN a.`timestamp` END) IS NULL,
    '17:00:00',
    FROM_UNIXTIME(MAX(a.`timestamp`), '%H:%i:%s')
  ) AS TimeOut,
  ROUND((
    IFNULL(
      MAX(CASE WHEN a.`type` = 'check out' THEN a.`timestamp` END),
      UNIX_TIMESTAMP(CONCAT('$filterDate', ' 17:00:00'))
    ) - MIN(a.`timestamp`)
  ) / 3600, 3) AS NumHours,
  IF(
    MAX(CASE WHEN a.`type` = 'check out' THEN 1 END) IS NULL,
    'Not checked out',
    ''
  ) AS Comments
FROM attendance a
JOIN employee e ON e.employee_id = a.employee_id
WHERE DATE(FROM_UNIXTIME(a.`timestamp`)) = '$filterDate'
  AND a.`type` IN ('check in','check out')
GROUP BY a.employee_id
ORDER BY e.full_name
";

[nadir]

It works fine. Thanks