I believe an IF statement would work in your situation
If(In>=Start and Out <= Stop,'X') as Flag
Table:
Load
Log,
Serv,
Date(Date,'DD/MM/YYYY') as Date,
Time(In,'hh:mm') as In ,
Time(Out,'hh:mm') as Out;
LOAD * INLINE
[
Log,Serv,Date,In,Out
123,Ortho,2/1/2017, 7:20,12:45
345,Ortho,2/1/2017, 9:15,11:20
567,Ortho,2/1/2017, 12:15,16:18
789,Uro,2/1/2017, 11:15,13:46
];
Left join(Table)
Load
Serv,
Date(Date,'DD/MM/YYYY') as Date,
Time(Start,'hh:mm') as Start ,
Time(Stop,'hh:mm') as Stop;
LOAD * INLINE
[
Serv,Date,Start,Stop
Ortho,2/1/2017,7:20,15:00
Uro,2/2/2017,7:20,17:00
];
Load *
Where
Flag='X';
Load
Log,
Serv,Date,
In,
Out,
Start,
Stop,
If(In>=Start and Out <= Stop,'X') as Flag
Resident Table;
Drop Table Table;
