Calculate working time SQL dataset

Question

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 6-Jun-22 12:50pm

Soft009

Updated 6-Jun-22 22:11pm

Add a Solution

Comments

[no name] 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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Richard Deeming · Answer 1 · 2022-06-06T22:11:00

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    |