How to calculate the total hours worked by employee per day?

Abhilash J A

10y2.1k

Hello Everyone,

Here is Employee's CheckIn and CheckOut table.

CardNo	HolderName	IODate	IOTime	IOGateNo	IOGateName	IOStatus	DepartmentNo
0118	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 21:52:25.000	01-2	Door #2(I/O)	Entry	3
0118	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 21:52:51.000	01-6	Door #2(I/O)	Exit	3
0118	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 21:52:59.000	01-5	Door #1(I/O)	Exit	3
0084	KENNETH WILSON	2016-01-02 00:00:00.000	2016-08-31 09:16:42.000	01-1	Door #1(I/O)	Entry	1
0084	KENNETH WILSON	2016-01-02 00:00:00.000	2016-08-31 09:16:49.000	01-2	Door #2(I/O)	Entry	1
0084	KENNETH WILSON	2016-01-02 00:00:00.000	2016-08-31 13:39:11.000	01-6	Door #2(I/O)	Exit	1
0084	KENNETH WILSON	2016-01-02 00:00:00.000	2016-08-31 13:39:17.000	01-5	Door #1(I/O)	Exit	1
0084	KENNETH WILSON	2016-01-02 00:00:00.000	2016-08-31 13:40:37.000	01-5	Door #1(I/O)	Exit	1
0076	GIRISH S	2016-01-02 00:00:00.000	2016-08-31 20:58:42.000	01-1	Door #1(I/O)	Entry	3
0076	GIRISH S	2016-01-02 00:00:00.000	2016-08-31 20:58:50.000	01-2	Door #2(I/O)	Entry	3
0076	GIRISH S	2016-01-02 00:00:00.000	2016-08-31 21:39:29.000	01-6	Door #2(I/O)	Exit	3
0076	GIRISH S	2016-01-02 00:00:00.000	2016-08-31 21:39:40.000	01-5	Door #1(I/O)	Exit	3
0023	HOUSE KEEPING	2016-01-04 00:00:00.000	2016-08-31 08:24:19.000	01-1	Door #1(I/O)	Entry	2
0023	HOUSE KEEPING	2016-01-04 00:00:00.000	2016-08-31 08:25:02.000	01-2	Door #2(I/O)	Entry	2
0084	KENNETH WILSON	2016-01-04 00:00:00.000	2016-08-31 08:33:46.000	01-1	Door #1(I/O)	Entry	1
0084	KENNETH WILSON	2016-01-04 00:00:00.000	2016-08-31 08:33:54.000	01-2	Door #2(I/O)	Entry	1
0070	VIPIN S SUBASH	2016-01-04 00:00:00.000	2016-08-31 08:48:35.000	01-1	Door #1(I/O)	Entry	3
0070	VIPIN S SUBASH	2016-01-04 00:00:00.000	2016-08-31 08:48:43.000	01-1	Door #1(I/O)	Entry	3
0070	VIPIN S SUBASH	2016-01-04 00:00:00.000	2016-08-31 08:48:52.000	01-2	Door #2(I/O)	Entry	3
0086	SEEMA ALEX A S	2016-01-04 00:00:00.000	2016-08-31 09:02:14.000	01-1	Door #1(I/O)	Entry	2
0086	SEEMA ALEX A S	2016-01-04 00:00:00.000	2016-08-31 09:02:21.000	01-2	Door #2(I/O)	Entry	2

1) How to calculate the hours worked in a day with respect to an employee using Sql query?

2) How to handle if an employee do not check out?

There is Input/Output Status (IOStatus) that is Entry and Exit that indicating Input/Output time with respect to status on IODate. And two doors (Door #1(I/O) and Door #2(I/O)) in my office.

Please, please help me, I am so confused.

Thanks Advance,

Abhilash

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Abhilash J A10y ago
HelloNitin Sontakke,
Excuse me Sir 1st table will not change. 2nd table in the sql server database. I want to create a procedure for calculating employee working hours, only passing date and Holder name in to that SP with respect to 2nd table in sql.
0
Nitin Sontakke10y ago
Personally, I am of the opinion that you should have a table in such a way that it should not affect your business if tomorrow they change the attendance machine vendor.
So, whatever scheduler you have written that will put the data in one table - we will NOT use that table. Consider this is a staging table (1st table).
From that table we will put data in another table, which we will use. Consider this is a real table (2nd table).
All your queries will run on 2nd table. While putting data in 2nd table you can ignore the door you wanted to ignore. 2nd table will have only employee id, io_date, io_datetime, etc. No card number, holder number columns, etc.
I want to implement abstraction. So if 1st table changes tomorrow, it should not matter to us because we are not looking at it.
+1

Abhilash J A10y ago

HelloNitin Sontakke,

We want to consider Door #2 entries and ignore the entries of Door #1. Apologies sir,

there is also Holder Number. This is actual table. Now we do not want employeeID. Isn't it, sir? because, there is HolderNo.

ID	CardNo	HolderNo	HolderName	IODate	IOTime	IOGateNo	IOGateName	IOStatus	DepartmentNo
1	0118	0085	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 12:14:39.000	01-1	Door #1(I/O)	Entry	3
2	0118	0085	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 12:14:47.000	01-2	Door #2(I/O)	Entry	3
3	0118	0085	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 12:14:59.000	01-6	Door #2(I/O)	Exit	3
4	0118	0085	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 12:15:13.000	01-2	Door #2(I/O)	Entry	3
5	0118	0085	ARTHUR WELLESLY	2016-01-01 00:00:00.000	2016-08-31 16:05:25.000	01-6	Door #2(I/O)	Exit	3

Nitin Sontakke10y ago
Hello,
Do you mean we will ignore the entries of Door #2?
Also, while doing the migration, I recommend that you take employee Id in the table and NOT THE CARD NUMBER.
There are lot of issues if you take card number.
When an employee leaves his/her card is given to another employee after few months.
There is also few cards given to visitors, etc.
0
Abhilash J A10y ago
Hello Bikesh Srivastava and Delpin Susai Raj,
I have written query like this. But it seem wrong. How can I ?
CREATETABLE[dbo].[Tble_IOData](
[ID][int]IDENTITY(1,1)NOTNULL,
[CardNo][varchar](50)NULL,
[HolderNo][varchar](50)NULL,
[HolderName][varchar](50)NULL,
[IODate][datetime]NULL,
[IOTime][datetime]NULL,
[IOGateNo][varchar](50)NULL,
[IOGateName][varchar](50)NULL,
[IOStatus][varchar](50)NULL,
[DepartmentNo][int]NULL
)
INSERT[dbo].[Tble_IOData]([CardNo],[HolderNo],[HolderName],[IODate],[IOTime],[IOGateNo],[IOGateName],[IOStatus],[DepartmentNo])
VALUES(N'0118',N'0085',N'ARTHURWELLESLY',CAST(0x0000A58000000000ASdatetime),CAST(0x0000A67300C9C714ASdatetime),N'01-1',N'Door#1(I/O)',N'Entry',3)
INSERT[dbo].[Tble_IOData]([CardNo],[HolderNo],[HolderName],[IODate],[IOTime],[IOGateNo],[IOGateName],[IOStatus],[DepartmentNo])
VALUES(N'0118',N'0085',N'ARTHURWELLESLY',CAST(0x0000A58000000000ASdatetime),CAST(0x0000A67300C9D074ASdatetime),N'01-2',N'Door#2(I/O)',N'Entry',3)
INSERT[dbo].[Tble_IOData]([CardNo],[HolderNo],[HolderName],[IODate],[IOTime],[IOGateNo],[IOGateName],[IOStatus],[DepartmentNo])
VALUES(N'0118',N'0085',N'ARTHURWELLESLY',CAST(0x0000A58000000000ASdatetime),CAST(0x0000A67300C9DE84ASdatetime),N'01-6',N'Door#2(I/O)',N'Exit',3)
INSERT[dbo].[Tble_IOData]([CardNo],[HolderNo],[HolderName],[IODate],[IOTime],[IOGateNo],[IOGateName],[IOStatus],[DepartmentNo])
VALUES(N'0118',N'0085',N'ARTHURWELLESLY',CAST(0x0000A58000000000ASdatetime),CAST(0x0000A67300C9EEECASdatetime),N'01-2',N'Door#2(I/O)',N'Entry',3)
INSERT[dbo].[test_IOData]([CardNo],[HolderNo],[HolderName],[IODate],[IOTime],[IOGateNo],[IOGateName],[IOStatus],[DepartmentNo])
VALUES(N'0118',N'0085',N'ARTHURWELLESLY',CAST(0x0000A58000000000ASdatetime),CAST(0x0000A673010928DCASdatetime),N'01-6',N'Door#2(I/O)',N'Exit',3)
SELECT
HolderNo,
HolderName,
CAST(IODateASdate)ASwork_dt,
CAST(IOTimeAStime)ASloginTime,
COALESCE((SELECT
MIN(CAST(b.IOTimeAStime))
FROMtest_IODataASb
WHEREa.HolderNo=b.HolderNo
ANDCAST(a.IODateASdate)=CAST(a.IODateASdate)
ANDCAST(b.IOTimeAStime)>=CAST(b.IOTimeAStime)
ANDa.IOStatus='Exit'),GETDATE())ASlogoutTime
FROMtest_IODataASa
WHEREa.IOStatus='Entry'
0
Abhilash J A10y ago
Hello Nitin Sontakke,
Very, very thank you for replay me sir. First, there are two doors Door #1 (I/O) and Door #2 (I/O). An employee who is 1st check in and 2nd check out is working hours in the office.
That is, 1st Entry - 2nd Exit. Actually, there is a swiping machine that is getting each employee check in and check out details 1st save in MS access database. First, I have done that, I have written an ADO.NET query to insert these employee data from MS Access DB to SQL Server by creating
sheduler .exe file on there mechine, that .exe now running each 2 min currectly.
I have discussed with HR about if an employee is not checked, then we want to calculate hours above check-in to checkout with respect to day and also an employee.
how can write a stored procedure with respect to those scenarios?
0
Nitin Sontakke10y ago
Hello Abhilash,
Hope you remember me! Did you spoke with HR / Management about the issue we discussed? That is the only solution. The solution is not technical. As soon as you get the answer to our question of Entry - Entry and Exit - Exit then rest all is very simple and I promise you I will write a stored procedure for you because I have written it before.
Please remember that I am hear to help you all the way to resolve this issue.
If you want my contact details to work off-line, please do let me know and I will be very happy to assist you!
If required, I am even ready to engage with your seniors to explain them the issue, as you wish!
+1
Bikesh Srivastava10y ago
Hi,
You need to use cumulitive sum logic.
like this.
selectempid,date(time)aswork_dt,timeaslogin
,coalesce(
(selectmin(time)
fromemplogasb
wherea.empid=b.empid
anddate(a.time)=date(b.time)
andb.time>=a.time
andb.state=1
),now())aslogout
fromemplogasa
wherea.state=0
0
Delpin Susai Raj10y ago
dba.stackexchange.com/questions/129898/total-hours-worked-per-employee-per-day
0

Delpin Susai Raj10y ago

select empid, date(time) as work_dt, time as login     , coalesce( (select min(time) from emplog as b             where a.empid = b.empid               and date(a.time) = date(b.time) and b.time >= a.time               and b.state = 1 ), now()) as logout from emplog as a  where a.state = 0

Madhanmohan Devarajan10y ago
Abhilash,
In your case may be you need to write stored procedure and call it using SQL Jobs everyday at specific time. The logic to calculate the hours are shown in the below image. But the time entry that you shared is not having proper Entry
and Exit logs, Multiple Entry without proper Exit are logged. With that you may not get accurate In/Out hours of employee worked.
+1