IntevalMatch on values that cross midnight

[andrewmo]

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

[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