1 Reply Latest reply: Mar 10, 2017 1:07 PM by Marcus Sommer RSS

    IntevalMatch on values that cross midnight

    Andrew Moberg

      Hello

       

      I have a load statement that is doing an interval match by minute of day.  It appears to be working only for cases that occur on the same date. Here is an example of an instance where a case crosses midnight and not getting any values for intervalmatch:

       

      CASES:

      LOAD * INLINE

      [

      LOG,SurgDt,InTime,OutTime

      240819,03/06/17,18:40:00,00:21:00

      ];

       

      MINODAY:

      Load interval(frac(recno()/1440),'h:mm') as MinOfDay autogenerate(1440);

       

      LOGTIME:

      LOAD DISTINCT LOG,

        InTime,

        OutTime,

        interval(InTime+(IterNo()-1)/1440, 'h:mm') as MinOfDay 

      RESIDENT CASES

      While InTime+(IterNo()-1)/1440<=OutTime;

       

      NoConcatenate

      MINS:

      Load MinOfDay,LOG Resident LOGTIME;

      INNER JOIN (MINS)

      IntervalMatch(MinOfDay,LOG)

      LOAD InTime, OutTime

      Resident LOGTIME;

       

       

      DROP TABLE LOGTIME;

       

      Any suggestions would be greatly appreciated.

       

      Thanks

        • Re: IntevalMatch on values that cross midnight
          Marcus Sommer

          I think if there is a day-shift included you will need to match your data on a timestamp and not only on time and you might need to adjust your source with something like this:

           

          CASES:

          LOAD

               LOG,SurgDt,

               date(SurgDt+InTime) as InTime,

               date(SurgDt+OutTime+if(OutTime<InTime,1,0)) as OutTime

          INLINE [

          LOG,SurgDt,InTime,OutTime

          240819,03/06/17,18:40:00,00:21:00];

           

          - Marcus