Calculating Employee Count based

[#1]

Hi All ,

Requirement is need to get the count of Employee based on the shift start , End TIme and Break Time.

Input:

PS ID	Date	[Log Type]	[Start Time]	[End Time]
123	6/1/2015	LogIn/LogOff	04/01/2015 05:00:00:000	04/01/2015 15:00:00:000
145	6/1/2015	LogIn/LogOff	04/01/2015 09:00:00:000	04/01/2015 18:30:00:000
178	6/1/2015	LogIn/LogOff	04/01/2015 13:00:00:000	04/01/2015 21:00:00:000
123	6/1/2015	Break	04/01/2015 08:00:00:000	04/01/2015 8:30:00:000
145	6/1/2015	Break	04/01/2015 11:00:00:000	04/01/2015 11:15:00:000
178	6/1/2015	Break	04/01/2015 17:00:00:000	04/01/2015 17:30:00:000

Login/Logoff - > Shift start and end of each employee

Break  - > Break should be subtracted based on the start and End Time.

Expected Output :

Interval	Count
0 -1	0
1 -2	0
2-3	0
3-4	0
4-5	0
5-6	1
6-7	1
7-8	1
8-9	0.5
9-10	2
10-11	2
11-12	1.75
12-13	2
13-14	3
14-15	3
15-16	2
16-17	2
17-18	1.5
18-19	1.5
19-20	1
20-21	1
21-22	0
22-23	0
23-24	0

Regards,

Senthik

[jagan]

Hi,

Please find attached file for solution.

Regards,

jagan.