I am trying to run query to fetch data of employees punch in and punch out month wise. Problem is both punch ( in and out) are in same column. I have two tables 'employee' and 'punch_time' as below
Table of Employee:
| Id | employee name |
|---|---|
| 1 | emp1 |
| 2 | emp2 |
| 3 | emp3 |
Table of Punch_time:
| PunchId | empId | punchTime |
|---|---|---|
| 1 | emp1 | 2022-05-01 10:02:25 |
| 2 | emp2 | 2022-05-01 10:00:23 |
| 3 | emp3 | 2022-05-01 18:15:04 |
| 4 | emp1 | 2022-05-01 18:10:25 |
| 5 | emp2 | 2022-05-01 18:00:00 |
| 6 | emp1 | 2022-05-02 10:00:05 |
| 7 | emp2 | 2022-05-02 10:10:15 |
| 8 | emp1 | 2022-05-02 18:02:25 |
| 9 | emp2 | 2022-05-02 18:02:25 |
I need result as below:
| emp. | Date | In. | Out. |
|---|---|---|---|
| emp1 | 2022-05-01 | 10:02:25 | 18:10:25 |
| emp2 | 2022-05-01 | 10:23:00 | 18:00:00 |
| emp3 | 2022-05-01 | 18:15:04 | |
| emp1 | 2022-05-02 | 10:00:05 | 18:02:25 |
| emp2 | 2022-05-02 | 10:10:15 | 18:02:25 |
| emp3 | 2022-05-02 | ||
| emp1 | 2022-05-03 | ||
| emp2 | 2022-05-03 | ||
| emp3 | 2022-05-03 |
Here is an approach to get your desired records. The only thing missing is it doesn't show a line for absenteeism.
SELECT
e.employeeName emp,
CAST(punchTime AS DATE) punchDate,
TIME(min(punchTime)) InTime,
if(max(punchTime)=min(punchTime),null,TIME(max(punchTime))) OutTime
FROM Employee e
JOIN Punch_time p ON e.employeeName = p.empId
GROUP BY punchDate, empId;
Also, Advise to use EmpID (numerical index) in your PunchTime table. Currently the relationship is on a text field. Employee.employeeName <> Punch_Time.empId NOT ADVISED