Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
A:
LOAD PAYCODE,
TR_LUNCHSTARTTIME,
TR_LUNCHENDTIME,
ACYUALIN,ACTUALOUT
FROM TABLE;
JOIN(A)
LOAD CARDNO as PAYCODE,
TIME(OFFICEPUNCH)AS MOVMENT
from table2;
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
MOVMENT |
08:43:00 |
10:58:00 |
13:17:00 |
18:13:00 |
and
ACTUALIN |
08:43:00 |
and
ACTUALOUT |
18:13:00 |
now i would like in pivot table
10:58:00 | 13:17:00 |
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....
like
MOVMENT |
08:43:00 |
10:58:00 |
13:17:00 13:47:00 |
18:13:00 |
there i want only 10:58:00 -
thanks in advance.......
Maybe like this. See attached example.
T1:
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
INLINE [
ID, TIME
1, 08:43
1, 10:58
1, 13:17
1, 13:47
1, 18:13
2, 09:03
2, 13:45
2, 18:23
3, 08:56
3, 13:11
3, 14:23
3, 17:56
];
T2:
load *,
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;
T3:
load ID,TIME
Resident T2 where fInBreak3 =0 and fEndTime=0;
drop table T2;
help meeeeeeeeeeeeeeeeeeeee
plzzzzzzzzzzzzzzzz
Maybe like this. See attached example.
T1:
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
INLINE [
ID, TIME
1, 08:43
1, 10:58
1, 13:17
1, 13:47
1, 18:13
2, 09:03
2, 13:45
2, 18:23
3, 08:56
3, 13:11
3, 14:23
3, 17:56
];
T2:
load *,
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;
T3:
load ID,TIME
Resident T2 where fInBreak3 =0 and fEndTime=0;
drop table T2;
hiii Gysbert Wassenaar
i can't open your application because i use personal edition.. could you please explain me
HIII
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