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_;
