Click here to Skip to main content
15,390,470 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a set of dataset collected from the biometric attendance system. It is stored in mssql server.

the dataset looks like this.

EmployeeId	AttendanceDate	AttendanceDateTime	PunchTime	PunchMethod
439D	2022-06-01 00:00:00.000	2022-06-01 02:04:10.000	2:4:10	OUT
439D	2022-06-02 00:00:00.000	2022-06-02 10:00:23.000	10:0:23	IN
439D	2022-06-02 00:00:00.000	2022-06-02 14:00:00.000	14:0:0	BreakOUT
439D	2022-06-02 00:00:00.000	2022-06-02 20:00:11.000	20:0:11	BreakIN
439D	2022-06-02 00:00:00.000	2022-06-02 12:59:41.000	12:59:41	BreakIN
439D	2022-06-02 00:00:00.000	2022-06-02 20:57:50.000	20:57:50	BreakOUT
439D	2022-06-03 00:00:00.000	2022-06-03 01:32:10.000	1:32:10	OUT
439D	2022-06-03 00:00:00.000	2022-06-03 10:18:56.000	10:18:56	IN
439D	2022-06-03 00:00:00.000	2022-06-03 14:01:13.000	14:1:13	BreakOUT
439D	2022-06-03 00:00:00.000	2022-06-03 13:00:30.000	13:0:30	BreakIN
439D	2022-06-04 00:00:00.000	2022-06-04 02:06:39.000	2:6:39	IN
439D	2022-06-04 00:00:00.000	2022-06-04 13:58:34.000	13:58:34	OUT
439D	2022-06-04 00:00:00.000	2022-06-04 20:56:50.000	20:56:50	BreakOUT
439D	2022-06-04 00:00:00.000	2022-06-04 20:00:16.000	20:0:16	BreakIN
439D	2022-06-04 00:00:00.000	2022-06-04 02:06:39.000	2:6:39	OUT
439D	2022-06-04 00:00:00.000	2022-06-04 13:58:34.000	13:58:34	BreakOUT
439D	2022-06-05 00:00:00.000	2022-06-05 10:17:43.000	10:17:43	OUT
439D	2022-06-05 00:00:00.000	2022-06-05 19:31:51.000	19:31:51	OUT
439D	2022-06-05 00:00:00.000	2022-06-05 00:30:14.000	0:30:14	IN
439D	2022-06-05 00:00:00.000	2022-06-05 13:02:01.000	13:2:1	BreakIN
439D	2022-06-05 00:00:00.000	2022-06-05 13:57:21.000	13:57:21	BreakOUT
439D	2022-06-05 00:00:00.000	2022-06-05 10:17:43.000	10:17:43	IN
439D	2022-06-05 00:00:00.000	2022-06-05 18:17:22.000	18:17:22	OUT
439D	2022-06-05 00:00:00.000	2022-06-05 00:30:14.000	0:30:14	OUT
439D	2022-06-05 00:00:00.000	2022-06-05 13:57:21.000	13:57:21	OUT
439D	2022-06-05 00:00:00.000	2022-06-05 13:02:01.000	13:2:1	IN



I need to find out the number of hours worked by the employee using the difference between IN and OUT time. But the problem is some times employees worked after mid night. then the OUT record will stored in next day.

What I have tried:

I tried pivot table using below query but it is not successful

create table #temp
(
    EmployeeID uniqueidentifier,
    EmpName varchar(300),
    AttendanceDate datetime,
    AttendanceDateTime datetime,
	--punchTime nvarchar(11),
	punchType nvarchar(10),

)

insert into #temp SELECT dbo.EmployeeMaster.EmployeeID,dbo.EmployeeMaster.FirstName+' '+ dbo.EmployeeMaster.LastName as Name, 
      CASE
    WHEN DATEDIFF(HOUR, '0:00:00' ,dbo.EmployeeAttendanceDevice.PunchTime)<4 THEN dbo.EmployeeAttendanceDevice.AttendanceDate-1
    ELSE dbo.EmployeeAttendanceDevice.AttendanceDate
END as AttendanceDate  ,
dbo.EmployeeAttendanceDevice.AttendanceDateTime,  
                         dbo.EmployeeAttendanceDevice.PunchMethod
                         FROM dbo.EmployeeAttendanceDevice INNER JOIN dbo.EmployeeMaster ON dbo.EmployeeAttendanceDevice.EmployeeId = dbo.EmployeeMaster.EmployeeID
						 WHERE (dbo.EmployeeAttendanceDevice.AttendanceDate BETWEEN CONVERT(DATETIME, '2022-06-01 00:00:00', 102) AND CONVERT(DATETIME, '2022-06-05 00:00:00', 102)) 
						 and dbo.EmployeeMaster.EmployeeID = '439D' ORDER BY AttendanceDateTime



DECLARE @cols  AS NVARCHAR(MAX)='';
DECLARE @query AS NVARCHAR(MAX)='';

SELECT @cols = @cols + QUOTENAME(punchType) + ',' FROM (select distinct punchType from #temp ) as tmp
select @cols = substring(@cols, 0, len(@cols)) --trim "," at end

set @query = 
'SELECT * from 
(
    select * from #temp
) src
pivot 
(
   max(AttendanceDateTime) for punchType in (' + @cols + ')
) piv'

execute(@query)
drop table #temp
Posted
Updated 6-Jun-22 22:11pm
Comments
Gerry Schmitz 6-Jun-22 20:05pm
   
You need to sort; pair the ins and outs (pair id); and join them.
Member 15627495 7-Jun-22 1:28am
   
The first thing to implement is a 'error look up'.
You won't have to calculate false input, or clock recording wrong input.

It's kind of 'status' of all the records for an employee.

Once all records are 'ok', then you can 'count' / 'add' / 'substract'

As I understand, you have 'Daily work', It's the big time between 'start of a job' and the 'end of a job' , then there are 'break'.

so you have :
count(IN) == count(OUT) by date order.
and
count(breakIN) == count(breakOUT) by date order too.
all are orders by Date, and one follow the other and so and so.

you can SUM(datediff(breakout - breakIN)) and substract(breakdatediff(breakout - breakin) separatly .

like said Gerry Schmitz , you always have a 'start date time', and a 'end date time'. It's absolutely 'pair', 'twins' to collapse by duration.


to light weight your temp table,
if all you're records are 'ok',
keep in my mind the duration of 'BIG job' contains duration of 'Break'.


All that to help you seeing an 'exit door' for your query to write.

1 solution

Seems simple enough:

1) Select the OUT records;
2) Select the closest IN record for the same employee;
3) Calculate the difference.

SQL
SELECT
    O.EmployeeId,
    I.AttendanceDateTime As TimeIn,
    O.AttendanceDateTime As TimeOut,
    DateDiff(minute, I.AttendanceDateTime, O.AttendanceDateTime) / 60. As HoursWorked
FROM
    YourTable As O
    CROSS APPLY
    (
        SELECT TOP 1 AttendanceDateTime
        FROM YourTable As I
        WHERE I.EmployeeId = O.EmployeeId
        And I.AttendanceDateTime <= O.AttendanceDateTime
        And I.PunchMethod = 'IN'
    ) As I
WHERE
    O.PunchMethod = 'OUT'
;
For your sample data, this produces:
| EmployeeId | TimeIn              | TimeOut             | HoursWorked |
|------------|---------------------|---------------------|-------------|
| 439D       | 2022-06-02 10:00:23 | 2022-06-03 01:32:10 | 15.533333   |
| 439D       | 2022-06-04 02:06:39 | 2022-06-04 13:58:34 | 11.866666   |
| 439D       | 2022-06-04 02:06:39 | 2022-06-04 02:06:39 | 0.000000    |
| 439D       | 2022-06-05 10:17:43 | 2022-06-05 10:17:43 | 0.000000    |
| 439D       | 2022-06-05 13:02:01 | 2022-06-05 19:31:51 | 6.483333    |
| 439D       | 2022-06-05 13:02:01 | 2022-06-05 18:17:22 | 5.250000    |
| 439D       | 2022-06-05 00:30:14 | 2022-06-05 00:30:14 | 0.000000    |
| 439D       | 2022-06-05 13:02:01 | 2022-06-05 13:57:21 | 0.916666    |
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900