4 Replies Latest reply: Oct 3, 2017 1:23 PM by Stefan Wühl RSS

    Utilization per hour

    kurleigh henry

      Hi Team,

       

      I need help on a utilization calculation that I am working on.

      I am trying to calculate utilization per hour for an imaging scanner. My issue is calculating utilization per hour over the year and I would like to add a ceiling of 120mins for exams over 2hours. I saw another example but it is not exactly what I am looking for.  I don't know how to start so any help would be great.

       

       

      Start_time                              End_time                                   Duration                    7am               8am              9am               10am      

       

      1/1/17 7:30am                         1/1/17 8:30am                         60mins                       30mins          30mins

      1/1/17 8:45am                         1/1/10 9:30am                         90mins                                              15mins         30mins        

       

       

      Please let me know if you need more information.

       

      Thanks,

      Kurleigh

        • Re: Utilization per hour
          Stefan Wühl

          Maybe start with something like this, use BucketHour and DurationBucket to create your statistics:

           

           

          Set TimestampFormat = 'M/D/YY h:mmtt';

           

          INPUT:

          LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

          Start_time,                              End_time                      

          1/1/17 7:30am,                         1/1/17 8:30am                  

          1/1/17 8:45am,                         1/1/17 9:30am                  

          ];

           

          LOAD RecID,

          Iterno() as BucketID,

          Hour(Start_time+(iterno()-1)*MakeTime(1)) as Bucket_Hour,

          Round(

          (RangeMin(End_time,Ceil(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1)))

          -RangeMax(Start_time,Floor(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1))))

          *60*24) as DurationBucket

          Resident INPUT

          WHILE Start_time+(iterno()-1)*MakeTime(1) <= Ceil(End_time,MakeTime(1));

            • Re: Utilization per hour
              kurleigh henry

              Thanks Stefan,

               

              I'll give it a try and let you know how it works out.

               

              Kurleigh

              • Re: Utilization per hour
                kurleigh henry

                Hi Stephan,

                 

                I am finding that my durations are not lining up correctly with the start time end time. Can you point out where this is happening? I loaded a few more time stamps

                 

                Set TimestampFormat = 'M/D/YY h:mmtt';

                 

                INPUT:

                LOAD Recno() as RecID, *, Round((End_time -Start_time)*(60*24)) as Duration INLINE [

                Start_time,                              End_time                      
                   

                1/1/17 9:45am,                         1/1/17 10:00am                  

                1/1/17 10:15am,                        1/1/17 12:30pm

                1/1/17 1:45pm,                         1/1/17 2:45pm                  

                1/1/17 3:15pm,                         1/1/17 5:00pm

                1/1/17 5:41pm,                         1/1/17 6:23pm                  

                1/1/17 7:15pm,                         1/1/17 10:30pm

                ];

                 

                LOAD RecID,

                Iterno() as BucketID,

                Hour(Start_time+(iterno()-1)*MakeTime(1)) as Bucket_Hour,

                Round(

                (RangeMin(End_time,Ceil(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1)))

                -RangeMax(Start_Time,Floor(Start_time+(iterno()-1)*MakeTime(1), MakeTime(1))))

                *60*24) as DurationBucket

                Resident INPUT

                WHILE Start_time+(iterno()-1)*MakeTime(1) <= Ceil(End_time,MakeTime(1));

                  • Re: Utilization per hour
                    Stefan Wühl

                    After correcting the incorrect field name spelling (Start_Time instead of Start_time), this is what the script returns:

                     

                          

                    RecIDDurationStart_timeEnd_timeBucket_HourBucketIDDurationBucket
                    1151/1/17 9:45am1/1/17 10:00am9115
                    21351/1/17 10:15am1/1/17 12:30pm10145
                    21351/1/17 10:15am1/1/17 12:30pm11260
                    21351/1/17 10:15am1/1/17 12:30pm12330
                    3601/1/17 1:45pm1/1/17 2:45pm13115
                    3601/1/17 1:45pm1/1/17 2:45pm14245
                    41051/1/17 3:15pm1/1/17 5:00pm15145
                    41051/1/17 3:15pm1/1/17 5:00pm16260
                    41051/1/17 3:15pm1/1/17 5:00pm1730
                    5421/1/17 5:41pm1/1/17 6:23pm17119
                    5421/1/17 5:41pm1/1/17 6:23pm18223
                    61951/1/17 7:15pm1/1/17 10:30pm19145
                    61951/1/17 7:15pm1/1/17 10:30pm20260
                    61951/1/17 7:15pm1/1/17 10:30pm21360
                    61951/1/17 7:15pm1/1/17 10:30pm22430

                     

                    could you point to the issue?