How do i compare two records with time component?

[amien]

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

[sunny_talwar · Accepted solution]

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;

[amien]

Thank! Works great!

I added :  date = Previous(date) and added date as field in the left join.