Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have these 4 records:
date, time from, time to, name, machine
1/1/2018, 11:00, 17:00, Mister X, Machina A
1/1/2018, 15:00, 19:00, Mister X, Machina B
1/1/2018, 11:00, 15:00, Mister Y, Machina C
1/1/2018, 16:00, 19:00, Mister Y, Machina D
the thing is that Mister X is planned double. He needs to work on Machine A and B at the same time (which is not possible). There is an overlap in time between 15:00 and 17:00
What i want to add is another field : conflict.
date, time from, time to, name, machine, conflict
1/1/2018, 11:00, 17:00, Mister X, Machina A, 1
1/1/2018, 15:00, 19:00, Mister X, Machina B, 1
1/1/2018, 11:00, 15:00, Mister Y, Machina C, 0
1/1/2018, 16:00, 19:00, Mister Y, Machina D, 0
Mister X has on both records a 1 because there is a conflict on time. Mister Y also has to work on 2 machines on the same day, but timewise there is no issue.
How can i solve this?
Thanks in advanced
May be this
Table:
LOAD *,
TimeStamp(date + [time from]) as Datetime_from,
TimeStamp(date + [time to]) as Datetime_to
INLINE [
date, time from, time to, name, machine
1/1/2018, 11:00, 17:00, Mister X, Machina A
1/1/2018, 15:00, 19:00, Mister X, Machina B
1/1/2018, 11:00, 15:00, Mister Y, Machina C
1/1/2018, 16:00, 19:00, Mister Y, Machina D
];
Left Join (Table)
LOAD name,
Max(Flag) as conflict
Group By name;
LOAD *,
If(name = Previous(name), If(Datetime_from <= Previous(Datetime_to), 1, 0), 0) as Flag
Resident Table
Order By name, Datetime_from;
May be this
Table:
LOAD *,
TimeStamp(date + [time from]) as Datetime_from,
TimeStamp(date + [time to]) as Datetime_to
INLINE [
date, time from, time to, name, machine
1/1/2018, 11:00, 17:00, Mister X, Machina A
1/1/2018, 15:00, 19:00, Mister X, Machina B
1/1/2018, 11:00, 15:00, Mister Y, Machina C
1/1/2018, 16:00, 19:00, Mister Y, Machina D
];
Left Join (Table)
LOAD name,
Max(Flag) as conflict
Group By name;
LOAD *,
If(name = Previous(name), If(Datetime_from <= Previous(Datetime_to), 1, 0), 0) as Flag
Resident Table
Order By name, Datetime_from;
Thank! Works great!
I added : date = Previous(date) and added date as field in the left join.