Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hey everyone,
I am trying to create a chart that shows how many employees are working per 15 minute interval.
My initial thought to solve this is to run a loop over it so a new record gets created for each 15 min gap between an employees shift start and end time.
For example - Incoming data looks like this:
PID | PAN | ShiftStartDateTime | ShiftEndDateTime |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 |
Output data would look like this:
PID | PAN | ShiftStartDateTime | ShiftEndDateTime | ShiftDateHour |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 09:00 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 09:15 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 09:30 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 09:45 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 10:00 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 10:15 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 10:30 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 10:45 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 11:00 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 11:15 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 11:30 |
00176263 | 00176263 | 27/09/17 09:00 | 27/09/17 12:00 | 27/09/17 11:45 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 12:00 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 12:15 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 12:30 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 12:45 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 13:00 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 13:15 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 13:30 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 13:45 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 14:00 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 14:15 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 14:30 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 14:45 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 15:00 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 15:15 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 15:30 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 15:45 |
00176263 | 00176263 | 27/09/17 12:00 | 27/09/17 16:06 | 27/09/17 16:00 |
I've been able to do this at hourly intervals, but completely stumped at how to do it at 15 minute intervals + have it round down to nearest interval. Start and end times can be at every minute of the day so the rounding will be an important aspect so there aren't any duplicate records.
Here is the script I used to achieve the hourly intervals, this wasn't great as it was rounding up to the next hour and for some reason, it missed hours for some employees...
WorkbrainData:
LOAD PID,
PAN,
ShiftStartDateTime,
ShiftEndDateTime,
timestamp(floor(ShiftStartDateTime + iterno()/24, 1/24)) as ShiftDateHour
Resident WORKEDWorkbrainData
While ShiftStartDateTime + (IterNo()-1)/24 <= ShiftEndDateTime;
Thanks in advance for the assist!!
Cheers
Carl
Hopefully this will do it
Table:
LOAD *,
TimeStamp(If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 14, 59)), 15/1440), Ceil(ShiftStartDateTime + ((IterNo()-2) * MakeTime(0, 15, 1)), 15/1440))) as ShiftDateHour
While Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) <= Ceil(ShiftEndDateTime, 15/1440);
LOAD * INLINE [
PID, PAN, ShiftStartDateTime, ShiftEndDateTime
00176263, 00176263, 27/09/17 09:00, 27/09/17 12:00
00176264, 00176264, 27/09/17 12:00, 27/09/17 16:06
00176265, 00176265, 27/09/17 12:06, 27/09/17 16:06
];
You're a legend Sunny! Looks to be working perfectly. Thanks so much!!!