Join query question.

Hi,

There are various ways to approach this, here's one example:

datatable (Container:string, Status:string, TimeGenerated:datetime)
[
  "C1", "Running", datetime('2018-11-13T19:27:16.000'),
  "C1", "Running", datetime('2018-11-13T19:32:17.000'),
  "C2", "Running", datetime('2018-11-13T19:31:10.000'),
  "C2", "Waiting", datetime('2018-11-13T19:29:17.000'),
  "C1", "Waiting", datetime('2018-11-13T19:28:14.000'),
  "C1", "Waiting", datetime('2018-11-13T19:29:15.000'),
  "C1", "Waiting", datetime('2018-11-13T19:30:19.000'),
  "C2", "Waiting", datetime('2018-11-13T19:25:12.000'),
  "C2", "Running", datetime('2018-11-13T19:26:10.000'),
]
| sort by Container asc, TimeGenerated asc
| extend Status_changed = (Container != prev(Container) or Status != prev(Status))
| where Status_changed == true
| extend Waiting_time = iff(Status=="Running" and prev(Status)=="Waiting", tostring(TimeGenerated-prev(TimeGenerated)), "null")

I've created a data table with records similar to what you suggest (not exactly the same).

First  - I sort the data by container and time of event.

Then - I calculate whether a row indicated a change in status for this specific container (from waiting to running or the other way).

I then keep only the records in which the status has changed - the others aren't needed for the calculation of waiting time.

Finally - I calculate the duration from the most recent waiting period, until the current running period.

HTH,

Noa