Maybe like this. See attached example.
LOAD *, if(ID=previous(ID),0,1) as fStartTime,
if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) ,1,0) as fInBreak1,
if(TIME<=MakeTime(14,00) and TIME>=maketime(13,00) and previous(TIME)<=MakeTime(14,00) and previous(TIME)>=maketime(13,00),1,0) as fInBreak2
if(ID=previous(ID),0,1) as fEndTime,
if(fInBreak2=1 or (fInBreak1=1 and previous(fInBreak2)=1),1,0) as fInBreak3
Resident T1 where fStartTime=0 order by ID,TIME desc;
drop table T1;
Resident T2 where fInBreak3 =0 and fEndTime=0;
drop table T2;
comm72924.qvw 134.2 K
in as your above script is superb work for me but according to my script how achive it
see the attached file ther i attached my scripting.i can't share personal data of my company
SCRIPTING.txt 1,002 bytes
hii guys. i tried a lot for getting the time between but can't get success on script side
TAKE AN EXAMPLE....
i have one table
LOAD CARDNO as PAYCODE,
there i want a MOVMENT i.e my time field and i want to peek that values from MOVMENT which are greater than ACTUALIN AND Less than LUNCHSTARTTIME
(min and max)
after that i want also peek those values which are greater than TR_LUNCHENDTIME and less than ACTUALOUT
HAVE AN EXAMPLE I HAVE MOVMENT OF PAYCODE
now i would like in pivot table
here,lunch time is 1:00:00 to 2:00:00,if in MOVMENT GETTING two times between LUNCHSTARTTIME & LUNCHENDTIME
so, i didnt want the values of MOVMENT between them....
there i want only 10:58:00 -
thanks in advance.......