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 |
Looks like you are in need of the IntervalMatch() function. This blog post by HIC explains it well.
You just need to have the interval as Hours.
I think No. Interval Match wont work for it.
I need to find the count of employees who were available during that particular slot
I use the IntervalMatch() function to do just that and it works perfectly.
Can you share the qvw file?
Hi,
Try this script
Temp:
LOAD
*,
Num(Hour(StartTime)) AS StartHour,
Num(Hour(EndTime)) AS EndHour,
Minute(StartTime) AS StartMinute,
Minute(EndTime) AS EndMinute;
LOAD
[PS ID],Date([Date]) AS Date, [Log Type], TimeStamp(TimeStamp#([Start Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS StartTime,
TimeStamp(TimeStamp#([End Time], 'MM/DD/YYYY hh:mm:ss:fff'), 'MM/DD/YYYY hh:mm:ss') AS EndTime
INLINE [
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];
LOAD
[PS ID], Date,
[Log Type],
StartHour + IterNo() - 1 AS Hour,
If([Log Type] = 'Break',
If(EndMinute > 0 AND StartHour + IterNo() - 1 = EndHour, -EndMinute/60, -1),
If(StartMinute > 0 AND IterNo() = 1, StartMinute/60, 1)) AS Value
RESIDENT Temp
WHILE StartHour + IterNo() - 1 <= EndHour;
Regards,
Jagan.
HI Jagan
Can you share your file
Hi,
Please find attached file for solution.
Regards,
Jagan.