Clock in/out events

[Mifa]

The database documentation for version 5.6.13 (which is the most recent one I've been able to find) says that the clock in/out events will be soon combined in a single row, making it easier to build queries about attendance. My question is simple: is this improvement available yet? If it isn't, has anyone written a query to retrieve total time worked per day and per employee? I couldn't find any in the query example page online.

Thanks!

[jordansparks]

No, this has not yet been done. A query would be very challenging because you would have to pair up your clock events. This would require an intermediate temp table.

[Mifa]

I know! That's why I was hoping someone had already built one... I'll give it a shot anyway. Thanks.

[Mifa]

This seems to be working, it certainly can be improved as it's kind of "brute force"... Also date format is for Canada. This query will return the time worked per day for a given employee over the period of time between startdate and enddate

SET @startdate := "2008-06-01";
SET @enddate := "2008-06-30";
SET @employee := "9"; /*9=LYNL, 10=NICF, 14=MELL, 15=TEMPH */

drop table if exists tmp;
drop table if exists tmp0;
drop table if exists tmp1;
drop table if exists tmp10;
drop table if exists tmp11;

create table tmp select *
from clockevent
where
EmployeeNum = @employee AND
TimeDisplayed <= @enddate AND
TimeDisplayed >= @startdate
order by
TimeDisplayed asc;

set @pos0 := 0;
create table tmp0 select
@pos0 := @pos0+1 AS POS0,
ClockEventNum AS CEN0,
TimeDisplayed AS TD0,
ClockIn AS CI0
from tmp
where
ClockIn = 0;

set @pos1 := 0;
create table tmp1 select
@pos1 := @pos1+1 AS POS1,
ClockEventNum AS CEN1,
TimeDisplayed AS TD1,
ClockIn AS CI1
from tmp
where
ClockIn = 1;

create table tmp10 select
CEN1,
CEN0,
TD1,
TD0,
CI1,
CI0
from tmp0, tmp1
where
POS1 = POS0;

create table tmp11 select
concat(FName, " ", LName) as Employee,
TD0,
TD1,
timediff(TD0,TD1) as Delta
from tmp10,employee
where
EmployeeNum = @employee;

select
Employee,
Date(TD0) as DateDay,
sec_to_time(sum(time_to_sec(Delta))) as Worktime
from tmp11
group by
DateDay