11 Replies Latest reply: Sep 28, 2017 7:49 PM by Carl Blunck RSS

    New record per 15 minute interval

    Carl Blunck

      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

      001762630017626327/09/17 12:0027/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

      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:15
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:30
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 12:45
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:00
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:15
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:30
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 13:45
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:00
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:15
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:30
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 14:45
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:00
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:15
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:30
      001762630017626327/09/17 12:0027/09/17 16:0627/09/17 15:45
      001762630017626327/09/17 12:0027/09/17 16:0627/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

        • Re: New record per 15 minute interval
          Sunny Talwar

          Try this

           

          WorkbrainData: 

          LOAD PID, 

               PAN, 

               ShiftStartDateTime, 

               ShiftEndDateTime, 

               TimeStamp(Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440)) as ShiftDateHour

          Resident WORKEDWorkbrainData  

          While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

           

          Tried with this sample script

           

          Table:

          LOAD *,

          TimeStamp(Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440)) as ShiftDateHour

          While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

          LOAD * INLINE [

              PID, PAN, ShiftStartDateTime, ShiftEndDateTime

              00176263, 00176263, 27/09/17 09:00, 27/09/17 12:00

          ];

            • Re: New record per 15 minute interval
              Carl Blunck

              Thanks Sunny!  That works great.  Is there any way you know of that can have the output table end at 11:45?

               

              My logic is that each interval represents someone working from (for example) 11:45 to 12:00.  Because the shift ends at 12:00, there shouldn't be a ShiftDateHour of 12:00 because the person isn't working between 12:00 and 12:15.

               

              Thanks

              Carl

                • Re: New record per 15 minute interval
                  Sunny Talwar

                  Change this

                  While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) <= Ceil(ShiftEndDateTime, 1/1440);

                   

                  to

                  While Ceil(ShiftStartDateTime + (IterNo() - 1) * 15/1440, 1/1440) < Ceil(ShiftEndDateTime, 1/1440);

                   

                  Removed the = from <=

                    • Re: New record per 15 minute interval
                      Carl Blunck

                      Amazing.  Last problem.

                       

                      Shifts that have a start time of (for example) 07:06 are breaking into increments as follows:

                       

                      07:06

                      07:21

                      07:36

                      etc.

                       

                      How can I round those back to 07:00, 07:15, 07:30.

                       

                      Cheers Sunny!

                        • Re: New record per 15 minute interval
                          Sunny Talwar

                          May be like this

                           

                          Table:

                          LOAD *,

                          TimeStamp(Floor(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440)) as ShiftDateHour

                          While Floor(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:06, 27/09/17 12:06

                          ];

                            • Re: New record per 15 minute interval
                              Carl Blunck

                              Morning Sunny,

                               

                              That fixes the times that do not start on a 15 minute interval, but for some reason, some of the times that do start on a 15 minute interval are creating a ShiftDateHour at the interval prior.

                               

                              For example:

                               

                              ShiftStartDateTime of 25/04/2016 07:00 has a ShiftDateHour of 25/04/2016 06:45:00

                               

                              But then others are completely fine.

                               

                              ShiftStartDateTime of 25/04/2016 08:00 has a ShiftDateHour of 25/04/2016 08:00:00

                               

                              Any ideas what is causing this?

                               

                              Attached is some data to help.

                               

                              Thanks for all your advice and help!

                              Carl

                                • Re: New record per 15 minute interval
                                  Sunny Talwar

                                  Try this

                                   

                                  Table:

                                  LOAD *,

                                  TimeStamp(If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440), Ceil(ShiftStartDateTime + ((IterNo()-2) * MakeTime(0, 15, 1)), 15/1440))) as ShiftDateHour

                                  While If(Frac(ShiftStartDateTime*1440/15) = 0, Ceil(ShiftStartDateTime + ((IterNo() - 1) * MakeTime(0, 15, 1)), 15/1440) < Ceil(ShiftEndDateTime, 15/1440), 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

                                  ];

                                    • Re: New record per 15 minute interval
                                      Carl Blunck

                                      Nearly there!  Small bug.

                                       

                                      For times that start on the 15 minute interval, the "2nd" interval isn't created.  As per output below:

                                          

                                      PIDPANShiftStartDateTimeShiftEndDateTimeShiftDateHour
                                      001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:00
                                      001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:30
                                      001762630017626327/09/17 09:0027/09/17 12:0027/09/17 9:45

                                       

                                      There should be a ShiftDateHour of 27/09/17 09:15 but there isn't.

                                       

                                      Issue doesn't occur for times that do not start on a 15 min interval

                                          

                                      PIDPANShiftStartDateTimeShiftEndDateTimeShiftDateHour
                                      001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:00
                                      001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:15
                                      001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:30
                                      001762650017626527/09/17 12:0627/09/17 16:0627/09/17 12:45

                                       

                                      Cheers

                                      Carl

                                        • Re: New record per 15 minute interval
                                          Sunny Talwar

                                          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

                                          ];

                          • Re: New record per 15 minute interval
                            Antonio Mancini

                            Hi Carl,

                            may be

                             

                            LOAD PID, 
                            PAN, 
                            ShiftStartDateTime, 
                            ShiftEndDateTime, 
                            timestamp(ShiftStartDateTime + (iterno()-1)*15/1440) as ShiftDateHour 
                            Resident WORKEDWorkbrainData 
                            While ShiftStartDateTime + (iterno()-1)*15/1440 <= ShiftEndDateTime
                            ;

                            Regards,

                            Antonio