11
Answers

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

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

Answers (11)