I have a code that does the following:
- Get data from the
HrAttLogs Table
- Enter the data of the
HrAttLogs Table at the HrAttLogsFormatted Table
I would like to know if the query below, which I will provide, can be improved? In terms of Performance and speed.
SELECT
FingerId,
ShiftId,
DateIn,
DateOut,
ScanIn,
ScanOut,
WorkhourIn,
WorkhourOut,
TIME_IN,
TIME_OUT
FROM
(
SELECT
i.FingerId,
i.ShiftId,
i.Date AS 'DateIn',
o.Date AS 'DateOut',
i.Time AS 'ScanIn',
CASE WHEN o.Time != i.Time THEN o.Time END AS 'ScanOut',
CASE
WHEN i.Time < i.ShiftIn_2 THEN i.WorkhourIn_1
WHEN i.Time < i.ShiftIn_3 THEN i.WorkhourIn_2
WHEN i.Time > i.ShiftIn_1 THEN i.WorkhourIn_3
END AS WorkhourIn,
/* Added check for Date too.. the last checking is for any overlapping Date */
CASE WHEN i.Status = 'Rolling' THEN
CASE
WHEN i.DateOut = i.Date AND i.Time > i.ShiftOut_1 THEN i.WorkhourOut_2
WHEN i.DateOut = i.Date AND i.Time > i.ShiftOut_2 THEN i.WorkhourOut_3
WHEN i.DateOut = i.Date AND i.Time > i.ShiftOut_3 THEN i.WorkhourOut_1
WHEN i.DateOut > i.Date THEN i.WorkhourOut_3
END
ELSE
CASE WHEN i.ShiftId != 'Rolling' THEN
CASE WHEN i.DateOut = i.Date AND o.Time > i.ShiftOut_2 THEN i.WorkhourOut_3
ELSE i.WorkhourOut_2
END
WHEN i.DateOut = i.Date AND o.Time > i.ShiftOut_3 THEN i.WorkhourOut_1
END
END AS WorkhourOut,
CASE
WHEN i.Time < i.ShiftIn_2 THEN TIMEDIFF(i.Time, i.WorkhourIn_1)
WHEN i.Time < i.ShiftIn_3 THEN TIMEDIFF(i.Time, i.WorkhourIn_2)
WHEN i.Time > i.ShiftIn_1 THEN TIMEDIFF(i.Time, i.WorkhourIn_3)
END AS 'TIME_IN',
CASE WHEN o.Time != i.Time THEN
CASE WHEN i.Status = 'Rolling' THEN
CASE WHEN o.Time > i.ShiftOut_3 THEN
CASE WHEN o.Time > i.ShiftOut_1 THEN
CASE WHEN o.Time > i.ShiftOut_2 THEN TIMEDIFF(o.Time, i.WorkhourOut_2)
ELSE TIMEDIFF(o.Time, i.WorkhourOut_1)
END
ELSE TIMEDIFF(o.Time, i.WorkhourOut_1)
END
ELSE TIMEDIFF(o.Time, i.WorkhourOut_3)
END
ELSE
CASE WHEN i.Status != 'Rolling' THEN
CASE WHEN o.Time > i.ShiftOut_1 THEN
CASE WHEN o.Time > i.ShiftOut_2 THEN TIMEDIFF(o.Time, i.WorkhourOut_1)
ELSE TIMEDIFF(o.Time, i.WorkhourOut_2)
END
END
END
END
END AS 'TIME_OUT',
i.ShiftIn_1,
i.ShiftIn_2,
i.ShiftIn_3,
i.ShiftOut_1,
i.ShiftOut_2,
i.ShiftOut_3,
i.WorkhourIn_2,
i.WorkhourIn_3,
i.WorkhourOut_2,
i.WorkhourOut_3,
i.Status
FROM
( /* INSERT here */
SELECT
i.FingerId,
fs.ShiftId,
CASE WHEN i.Status = 0 THEN
MIN(i.Date)
END AS 'Date',
-- MIN(i.Date) AS 'Date',
/* Added CASE expression to deal with overlapping attendance Date */
CASE WHEN i.Status = 0 AND MIN(i.Time) >= '19:00:00'
THEN DATE(DATE_ADD(i.Date, INTERVAL + 1 DAY))
ELSE
DATE(DATE_ADD(i.Date, INTERVAL + s.DayOut DAY))
END AS 'DateOut',
CASE WHEN i.Status = 0 THEN
i.Time
END AS 'Time',
-- MIN(i.Time) AS 'Time',
i.Status,
s.ShiftIn_1,
s.ShiftIn_2,
s.ShiftIn_3,
s.ShiftOut_1,
s.ShiftOut_2,
s.ShiftOut_3,
s.WorkhourIn_1,
s.WorkhourIn_2,
s.WorkhourIn_3,
s.WorkhourOut_1,
s.WorkhourOut_2,
s.WorkhourOut_3
FROM HrAttLogs AS i
INNER JOIN HrEmployee AS fs ON fs.FingerId = i.FingerId
INNER JOIN HrEmployeeShift AS s ON s.Id = fs.ShiftId
WHERE
i.Time >= s.ShiftIn_1
AND i.Date >= '2020-04-07'
AND i.Date <= '2020-04-09'
AND i.MachineIp = '10.20.20.73'
GROUP BY
i.Id,
i.FingerId,
i.Date
) AS i
LEFT JOIN (
SELECT
o.FingerId,
CASE WHEN o.Status = 1 THEN
MAX(o.Date)
END AS 'Date',
CASE WHEN o.Status = 1 THEN
MAX(o.Time)
END AS 'Time',
o.Status
FROM
HrAttLogs AS o
WHERE
o.Date >= '2020-04-07'
AND o.Date <= '2020-04-09'
AND o.MachineIp = '10.20.20.73'
GROUP BY
o.Id,
o.FingerId,
o.Date
ORDER BY
Date ASC,
Status ASC
) AS o ON i.FingerId = o.FingerId
AND o.Date = i.DateOut
AND o.Time >= '00:00:00'
) AS q
WHERE
FingerId = 61
AND FingerId IS NOT NULL
AND ShiftId IS NOT NULL
AND DateIn IS NOT NULL
AND DateOut IS NOT NULL
AND ScanIn IS NOT NULL
AND WorkhourIn IS NOT NULL
AND WorkhourOut IS NOT NULL
AND TIME_IN IS NOT NULL
AND TIME_OUT IS NOT NULL
ORDER BY
q.FingerId ASC,
DateIn ASC
For more details, I save it here
