Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
Please find attached file for solution.
Regards,
jagan.