Combine value only once with another value

[#1]

I want to reduce a table by combining two lines into one where there is a connection. I have the following data

Id, Next, Begin, End

1, 2, 08:30, 10:00

1, 2, 15:20, 16:40

2, , 17:00, 18:30,

2, , 19:20, 21:00

Each entry in "next" has exactly ONE corresponding in "id", for example the the first and the third line belong together and the second and the forth. The minimal difference between the end-time of the first and the begin time of the second makes clear, which entry must be picked if one id occurs several times. But one value should only be connected once, so line 1 has a waiting time of 7 hours before line 3 begins. and though there is only a difference of 20 minutes between line 2 and 3, line 3 could not be used a second time and instead there is a waiting time of 2 hours and 40 minutes.

but right now i could not restrict it that way and therefore line 3 is connected to line 1 and line 2 which i do not intend.

can anyone help?

[swuehl]

Maybe like this:

[Data]:
LOAD * INLINE [
    Id, Next, Begin, End
    1, 2, 08:30, 10:00
    1, 2, 15:20, 16:40
    2, , 17:00, 18:30
    2, , 19:20, 21:00
];


[Temp_Combine]:
LOAD Id as TId,
  End as TEnd,
  Next as TCId
RESIDENT [Data];


LEFT JOIN([Temp_Combine])
LOAD
  Id as TCId,
  Begin as TNextBegin
RESIDENT [Data]
WHERE EXISTS(Id);




[Temp_Difference]:
LOAD TId,
  TEnd,
  TCId,
  TNextBegin,
  If(IsNull(TCId), 0, Time(TNextBegin - TEnd, 'hh:mm')) as [Wait]
RESIDENT [Temp_Combine]
WHERE TNextBegin - TEnd >= 0;
DROP TABLE [Temp_Combine];


[Waiting]:
LOAD TId, TEnd, TCId, TNextBegin, Wait, TEnd as Check1, TNextBegin as Check2
RESIDENT [Temp_Difference]
Where Not Exists(Check1, TEnd) and Not Exists(Check2, TNextBegin)
Order by TId, TEnd, Wait;


drop table [Temp_Difference];