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

Question

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

Nitin Sontakke · Answer

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.

Nitin Sontakke · Answer

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!

Madhanmohan Devarajan · Answer

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.

Mani Kandan · Answer

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.

Mani Kandan · Answer

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 Sontakke · Answer

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.

Mani Kandan · Answer

Hello Bikesh Srivastava and Delpin Susai Raj I have written query like this. But it seem wrong. How can I ? CREATE TABLE [dbo].[Tble_IOData]( [ID][ int ]IDENTITY(11) NOT NULL  [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 (0x0000A58000000000 AS datetime) CAST (0x0000A67300C9C714 AS datetime)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 (0x0000A58000000000 AS datetime) CAST (0x0000A67300C9D074 AS datetime)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 (0x0000A58000000000 AS datetime) CAST (0x0000A67300C9DE84 AS datetime)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 (0x0000A58000000000 AS datetime) CAST (0x0000A67300C9EEEC AS datetime)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 (0x0000A58000000000 AS datetime) CAST (0x0000A673010928DC AS datetime)N '01-6' N 'Door#2(I/O)' N 'Exit' 3) SELECT HolderNo HolderName CAST (IODate AS date ) AS work_dt CAST (IOTime AS time ) AS loginTime COALESCE (( SELECT MIN ( CAST (b.IOTime AS time )) FROM test_IOData AS b WHERE a.HolderNo=b.HolderNo AND CAST (a.IODate AS date )= CAST (a.IODate AS date ) AND CAST (b.IOTime AS time )>= CAST (b.IOTime AS time ) AND a.IOStatus= 'Exit' )GETDATE()) AS logoutTime FROM test_IOData AS a WHERE a.IOStatus= 'Entry'

Mani Kandan · Answer

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?

Bikesh Srivastava · Answer

Hi You need to use cumulitive sum logic. like this. 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

Delpin Susai Raj · Answer

dba.stackexchange.com/questions/129898/total-hours-worked-per-employee-per-day

Delpin Susai Raj · Answer

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

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