I have written one MySQL Query which is finding missing attendance from clockInTest table and then inserted a new row for the end time of that day. But, I also need to calculate the total office hours and break time of that days (which are newly inserted), and that I have to update the WorkDay table with timeSpan and breakTime. Following are the sample data I am using:

Before executing my Query:

After executing the following Query:

START TRANSACTION;
INSERT INTO ClockInTest (PartnerId, Timestamp, WorkDayId, UserId, EmployeeId, Type )
SELECT a.PartnerId, CONCAT(DATE(a.TimeStamp),' 23:59:00'), a.WorkDayId, a.UserId, a.EmployeeId, 'End'
FROM  ClockInTest a
WHERE a.Type = 'Start' 
AND a.DeletedAt IS NULL
AND a.TIMESTAMP <= NOW() - INTERVAL 1 DAY
AND NOT EXISTS (
  SELECT 1
  FROM  ClockInTest b
  WHERE b.EmployeeId = a.EmployeeId
  AND a.WorkDayId = b.WorkDayId
  AND a.Timestamp < b.Timestamp
  AND b.Type = 'End'
  AND b.DeletedAt IS NULL
) GROUP BY a.WorkDayId, a.EmployeeId;
SELECT * FROM clockintest WHERE WorkDayId IN (
SELECT DISTINCT c.WorkDayId FROM clockintest c WHERE c.Id >= LAST_INSERT_ID());
COMMIT;

clockInTest table after the query execution:

So, the above table missing data are added. Now, I have to calculate the total working hours (timeSpan) and break time for each WorkDayId and after that, I have to update the WorkDay table which is like following:

WorkDay table

Expected Result in WorkDay Table:

** TimeSpan and BreakTime in miliseconds

CodePudding user response：

I guess we can combine tables data for each Start type, and find the respective End time and the Next Start time if any

   WorkDayId | EmployeeId | Start | End | Next Start
    148 | 1 | 2021-10-25 08:00:00 | 2021-10-25 23:59:00 
    149 | 1 | 2021-10-26 08:00:00 | 2021-10-26 10:00:00 | 2021-10-26 12:00:00
    149 | 1 | 2021-10-26 12:00:00 | 2021-10-26 23:59:00 | 

Then the expected result is simply sum up of the time diff

SELECT 
  a.WorkDayId, 
  a.EmployeeId, 
  SUM(TIMESTAMPDIFF(SECOND, a.`TimeStamp`, b.`TimeStamp`)) * 1000 AS timespan,
  SUM(CASE WHEN c.`Timestamp` IS NULL THEN 0 ELSE TIMESTAMPDIFF(SECOND, b.`TimeStamp`, c.`TimeStamp`) END) * 1000 AS breakTime
FROM clockInTest a 
JOIN clockInTest b 
  ON a.EmployeeId = b.EmployeeId 
     AND a.WorkDayId = b.WorkDayId 
     AND b.`Timestamp` > a.`Timestamp`
LEFT JOIN clockInTest c 
  ON a.EmployeeId = c.EmployeeId 
     AND a.WorkDayId = c.WorkDayId 
     AND c.`Type` = 'Start' 
     AND c.`Timestamp` > b.`Timestamp` 
WHERE a.`Type` = 'Start'
AND b.`Type` = 'End'
AND NOT EXISTS (
  SELECT 1
  FROM clockInTest d
  WHERE a.EmployeeId = d.EmployeeId
  AND a.WorkDayId = d.WorkDayId
  AND (
        ( d.`Timestamp` > a.`Timestamp` AND d.`Timestamp` < b.`Timestamp` )
     OR ( d.`Timestamp` > b.`Timestamp` AND d.`Timestamp` < c.`Timestamp` )
  ) 
)
GROUP BY a.WorkDayId, a.EmployeeId
ORDER BY a.WorkDayId, a.EmployeeId

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=eb3b1fef815f1201554fdca683a8448f

To update the results to WorkDay table, assume the WorkDayId & EmployeeId records exist and timespan & breakTime are empty

UPDATE WorkDay w
JOIN (
  ... // the above select query
) g ON w.id = g.WorkDayId AND w.EmployeeId = g.EmployeeId
SET w.TimeSpan = g.timespan, w.BreakTime = g.breakTime
;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=2262e5156c0a991eddbbb39ebacfd3bf