Get All Punch in and Out for Each Employee

SQL query get first punch and lastpunch for every employee

You can do conditional aggregation :

select employeeid, In, Out,
       dateadd(second, datediff(second, in, out), 0) as Hours
from(select employeeid,
            min(case when AccessCode = 'I' then timestamp end) as In,
            max(case when AccessCode = 'O' then timestamp end) as Out
     from table t
     where lunchtype = 'N'
     group by employeeid, convert(date, times)
    ) t;

How to get all punches of employee in a row?

You can pivot the timestamps -- which seems to be the gist of your question -- using conditional aggregation:

select id, convert(date, dt),
       max(case when seqnum = 1 then dt end) as event_1,
       max(case when seqnum = 2 then dt end) as event_2,
       max(case when seqnum = 3 then dt end) as event_3,
       max(case when seqnum = 4 then dt end) as event_4
from (select e.*,
             row_number() over (partition by id, convert(date, dt) order by dt) as seqnum
      from Trnevents e
     ) e
group by id, convert(date, dt);

It seems very dangerous to try to impute what the actual events are:

• People may take no breaks.
• People may take more than one break.
• People may need to leave during the day.
• Shifts may go past midnight.
• Equipment may "malfunction" and either miss an event or record an event twice.

Without an identification of the type of event, it is just not reasonable to try to figure out anything more from the data you have provided.

MySQL fetch Punch in and punch out data of employee

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;

FIDDLE LINK

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

SQL query to Return latest entry per user in mysql utilizing two tables

I found that this code works..

SELECT p.emp_id, CONCAT(e.last, ', ', e.first) AS name, p.status, DATE_FORMAT(p.tistamp, '%d/%m/%Y') AS date, TIME_FORMAT(p.tistamp, '%H:%i') AS time FROM punches p, employees e WHERE p.emp_id=e.id AND p.id = (SELECT MAX(p.id) FROM punches p WHERE e.id=p.emp_id) ORDER BY e.last ASC

---

Related Topics