Interval match help

Ayoub — Tue, 29 Jun 2021 08:26:53 GMT

Hello Community,

i'm working on a new project for my company and i need your help. i want to match time extracted from a field into an interval of time.
the code who makes intervals work as a charm, and the extraction code also. but my problem is on interval match time.
I really need help on this. here's below the code i wrote.

and thank u in advance

//****************************************************** Période ****************************************\\
Time:
LOAD * INLINE [
Start, Finish
09:00, 13:00
14:00, 18:00
];

Dumb:
LOAD *,'$(Periode)' as vPeriode
INLINE [Dumb,
0];

LET P=if(FieldValue('vPeriode', 1)='15 Minutes',96,if(FieldValue('vPeriode', 1)='30 Minutes',48,if(FieldValue('vPeriode', 1)='1 H',24,if(FieldValue('vPeriode', 1)='2 H',12))));

Time_:
Load *,

Time(RangeMax(Start, Floor(Start,1/$(P))+(iterno()-1)/$(P)),'hh:mm') as NewStart,

if(Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm')=time#(time('17:59','hh:mm')),time#('18:00','hh:mm'),
if(Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm')=time#(time('12:59','hh:mm')),time#('13:00','hh:mm'),
Time(RangeMin(Finish, Floor(Start,1/$(P))+iterno()/$(P))-Time('00:01','hh:mm'),'hh:mm'))) as NewFinish

Resident Time

While Start+(iterno()-1)/$(P) < Finish;

DROP Table Time;
//**************************************************************************************************************\\

OctContH:
LOAD ID,
(date(DATEHISTORIQUE, 'hh:mm')) as Time,
date(DATEHISTORIQUE,'DD/MM/YYYY hh:mm:ss') as DATEHISTORIQUE
Where not match(WeekDay(DATEEVENEMENT),'dim.')
and (not(Match(WeekDay(DATEEVENEMENT),'sam.') and time#(time(DATEEVENEMENT, 'hh:mm:ss.fff' )) >= time#(time( '15:00:00.000' , 'hh:mm:ss.fff' ))))
and not Match(date(DATEEVENEMENT,'DD/MM/YYYY'),$(vFiltre));

SQL SELECT ID,
DATEEVENEMENT,
DATEHISTORIQUE,

FROM OCT

where DATEEVENEMENT BETWEEN

TO_DATE('$(vInputDate_du)', 'DD/MM/YYYY') AND TO_DATE('$(vInputDate_au)', 'DD/MM/YYYY');

Inner Join IntervalMatch ( Time )
LOAD
NewStart ,
NewFinish
Resident Time_;

DROP Table Time_;

topic Interval match help in App Development

Interval match help