15 Replies Latest reply: Dec 7, 2017 4:09 AM by Stefan Wühl RSS

    Show minutes used in Hour

    james eckstein

      Hi all,

       

      I have a table that looks like this

      IDDateStart TimeEnd Time
      A11/8/20179:30:0010:30:00
      B11/8/201711:30:0013:00:00
      C11/9/20177:30:0010:00:00
      D11/9/201713:30:0014:00:00

       

      I am looking to preform some back end scripting to get the following result

       

      IDDateHourMinutes Used in Hour

      A

      11/8/2017830
      A11/8/2017930
      B11/8/20171030
      B11/8/20171160
      C11/9/2017630
      C11/9/2017760
      C11/9/2017860
      D11/9/20171230

       

       

      What I am looking for is to show how many minutes were used in each hour. For example

      ID C starts at 7:30 am and ends at 10:00 am.

       

      30 minutes is used in the 6th hour, 60 minutes used in the 9th hour, and 60 mintues used in the 10th hour.

       

      (The hour count starts at 0 so midnight-1 am is considered hour 0, 1am-2am is considered hour 1 etc.)

       

      Please let me know if you have any ideas!!

       

      Thanks in advanced!!

        • Re: Show minutes used in Hour
          Sunny Talwar

          May be this

           

          Table:

          LOAD *,

          Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

          Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

          If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

          While [Start Time] + MakeTime(IterNo() - 1) <= [End Time];

          LOAD * INLINE [

              ID, Date, Start Time, End Time

              A, 11/8/2017, 9:30:00, 10:30:00

              B, 11/8/2017, 11:30:00, 13:00:00

              C, 11/9/2017, 7:30:00, 10:00:00

              D, 11/9/2017, 13:30:00, 14:00:00

          ];

            • Re: Show minutes used in Hour
              james eckstein

              Sunny,

               

              This is close. when applied to my real data set it seems that some hours are getting left out. For example I have a ID that has the following

               

              ID,Date,Start,End

              A,2017-06-03,7:30:00,9:08:00

               

              The result this code is giving me is the following

               

              ID,Date,Hour,Start,End

              A,2017-06-03,6,30

              A,2017-06-03,7,60


              However there is no row for the 8th hour with 8 mins.

                • Re: Show minutes used in Hour
                  Sunny Talwar

                  Try this

                   

                  Table:

                  LOAD *

                  Where [Minutes Used in Hour] <> 0;

                  LOAD *,

                  Hour([Start Time] + MakeTime(IterNo() - 1))-1 as Hour,

                  Interval(If([Start Time] >= [Start Time] + MakeTime(IterNo() - 1), Ceil([Start Time], 1/24) - [Start Time],

                  If([End Time] <= [Start Time] + MakeTime(IterNo() - 1), [End Time] - Floor([End Time], 1/24), 1/24)), 'mm') as [Minutes Used in Hour]

                  While Hour([Start Time] + MakeTime(IterNo() - 1)) <= Hour([End Time]);

                  LOAD * INLINE [

                      ID, Date, Start Time, End Time

                      A, 11/8/2017, 9:30:00, 10:30:00

                      B, 11/8/2017, 11:30:00, 13:00:00

                      C, 11/9/2017, 7:30:00, 10:00:00

                      D, 11/9/2017, 13:30:00, 14:00:00

                      E, 3/6/2017,  7:30:00,  9:08:00

                  ];

                    • Re: Show minutes used in Hour
                      james eckstein

                      Sunny,

                       

                      This is giving me the same issue. Some hours usually the last hour is getting dropped off

                          • Re: Show minutes used in Hour
                            james eckstein

                            Is there a way to do this by half hour intervals instead of full hour?

                              • Re: Show minutes used in Hour
                                Stefan Wühl

                                Yes, should be possible, chnage the code to something like

                                 

                                LOAD RecID,

                                Iterno() as BucketID,

                                Time(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30))) as Bucket_Time,

                                Round(

                                (RangeMin(End_time,Floor(Start_time+(iterno())*MakeTime(0,30), MakeTime(0,30)))

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

                                *60*24) as DurationBucket

                                Resident INPUT

                                WHILE Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)) < End_time;

                                  • Re: Show minutes used in Hour
                                    james eckstein

                                    The time function for the field Bucket_Time is causing me issues. there are duplicate values here. I believe because this function is still somehow associated with date

                                      • Re: Show minutes used in Hour
                                        Stefan Wühl

                                        Use

                                         

                                        Time#(Time(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)))) as Bucket_Time,

                                          • Re: Show minutes used in Hour
                                            Stefan Wühl

                                            The complete script:

                                             

                                            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

                                            1/1/17 9:23pm,    1/3/17 10:10am

                                            ];

                                             

                                             

                                            LOAD RecID,

                                            Iterno() as BucketID,

                                            Time#(Time(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)))) as Bucket_Time,

                                            Round(

                                            (RangeMin(End_time,Floor(Start_time+(iterno())*MakeTime(0,30), MakeTime(0,30)))

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

                                            *60*24) as DurationBucket

                                            Resident INPUT

                                            WHILE Timestamp#(Timestamp(Floor(Start_time+(iterno()-1)*MakeTime(0,30),MakeTime(0,30)))) < End_time;

                                            • Re: Show minutes used in Hour
                                              james eckstein

                                              What about taking this down to the minute lvl?

                                                • Re: Show minutes used in Hour
                                                  Stefan Wühl

                                                  The basic idea is always the same, create a loop and create your buckets:

                                                   

                                                  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

                                                  1/1/17 9:23pm,    1/3/17 10:10am

                                                  ];

                                                   

                                                  LOAD RecID,

                                                  Iterno() as BucketID,

                                                  Time#(Time(Round(Start_time+(iterno()-1)*MakeTime(0,1),MakeTime(0,1)))) as Bucket_Time,

                                                  1 as DurationBucket

                                                  Resident INPUT

                                                  WHILE iterno()-1 < Round((End_time-Start_time)*24*60);

                                        • Re: Show minutes used in Hour
                                          james eckstein

                                          How would I change this code to do the same calculation for every minute rather then half hour?

                                           

                                          Thanks in advance!

                                • Re: Show minutes used in Hour
                                  Thiago Pinho

                                  is like this?