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