Thiết kế website giá rẻ

Question

Here is the KQL table in which I am trying to calculate downtime between the current trolley_start timestamp and the previous trolley_end timestamp for each associate.

datatable (cart_id: int, id: int, Associate_1: int, Associate_2: int, Trolley_Start: datetime, Begin_Picking: datetime, End_Picking: datetime, Trolley_End: datetime)
[
    1, 111, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T09:37:20.123Z), datetime(2024-08-13T10:10:45.789Z), datetime(2024-08-13T10:31:12.345Z),
    1, 112, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T10:14:18.567Z), datetime(2024-08-13T10:31:50.234Z), datetime(2024-08-13T10:31:12.345Z),
    2, 113, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:15:09.456Z), datetime(2024-08-11T14:20:30.789Z), datetime(2024-08-11T16:01:11.001Z),
    2, 114, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:33:45.123Z), datetime(2024-08-11T16:00:55.456Z), datetime(2024-08-11T16:01:11.001Z),
    3, 115, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T06:23:20.111Z), datetime(2024-08-12T09:03:05.789Z), datetime(2024-08-12T07:36:14.678Z),
    3, 116, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T07:32:18.789Z), datetime(2024-08-12T07:34:12.123Z), datetime(2024-08-12T07:36:14.678Z),
    4, 111, 111, 114, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:18:45.789Z), datetime(2024-08-13T10:25:45.789Z), datetime(2024-08-13T10:40:45.789Z),
    4, 114, 111, 114, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:26:45.789Z), datetime(2024-08-13T10:38:45.789Z), datetime(2024-08-13T10:40:45.789Z),
    5, 111, 111, 112, datetime(2024-08-13T10:42:45.789Z), datetime(2024-08-13T10:43:45.789Z), datetime(2024-08-13T10:50:45.789Z), datetime(2024-08-13T10:59:45.789Z),
    5, 112, 111, 112, datetime(2024-08-13T10:42:45.789Z), datetime(2024-08-13T10:52:45.789Z), datetime(2024-08-13T10:58:45.789Z), datetime(2024-08-13T10:59:45.789Z)
]
| partition hint.strategy=native by cart_id
(
    order by id asc
    | serialize row_num = row_number()
)
| extend 
    Trolley_Start = iff(row_num == 1, Begin_Picking, Trolley_Start),
    Trolley_End = iff(row_num == 2, End_Picking, Trolley_End)
| project cart_id, id, Associate_1, Associate_2, Trolley_Start, Begin_Picking, End_Picking, Trolley_End
| order by cart_id, id asc

So far I have tried:

datatable (cart_id: int, id: int, Associate_1: int, Associate_2: int, Trolley_Start: datetime, Begin_Picking: datetime, End_Picking: datetime, Trolley_End: datetime)
[
    1, 111, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T09:37:20.123Z), datetime(2024-08-13T10:10:45.789Z), datetime(2024-08-13T10:31:12.345Z),
    1, 112, 111, 112, datetime(2024-08-13T09:35:15.991Z), datetime(2024-08-13T10:14:18.567Z), datetime(2024-08-13T10:31:50.234Z), datetime(2024-08-13T10:31:12.345Z),
    2, 113, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:15:09.456Z), datetime(2024-08-11T14:20:30.789Z), datetime(2024-08-11T16:01:11.001Z),
    2, 114, 113, 114, datetime(2024-08-11T14:13:05.678Z), datetime(2024-08-11T14:33:45.123Z), datetime(2024-08-11T16:00:55.456Z), datetime(2024-08-11T16:01:11.001Z),
    3, 115, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T06:23:20.111Z), datetime(2024-08-12T09:03:05.789Z), datetime(2024-08-12T07:36:14.678Z),
    3, 116, 115, 116, datetime(2024-08-12T06:22:14.333Z), datetime(2024-08-12T07:32:18.789Z), datetime(2024-08-12T07:34:12.123Z), datetime(2024-08-12T07:36:14.678Z),
    4, 111, 111, 114, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:18:45.789Z), datetime(2024-08-13T10:25:45.789Z), datetime(2024-08-13T10:40:45.789Z),
    4, 114, 111, 114, datetime(2024-08-13T10:15:45.789Z), datetime(2024-08-13T10:26:45.789Z), datetime(2024-08-13T10:38:45.789Z), datetime(2024-08-13T10:40:45.789Z),
    5, 111, 111, 112, datetime(2024-08-13T10:42:45.789Z), datetime(2024-08-13T10:43:45.789Z), datetime(2024-08-13T10:50:45.789Z), datetime(2024-08-13T10:59:45.789Z),
    5, 112, 111, 112, datetime(2024-08-13T10:42:45.789Z), datetime(2024-08-13T10:52:45.789Z), datetime(2024-08-13T10:58:45.789Z), datetime(2024-08-13T10:59:45.789Z)
]
| partition hint.strategy=native by cart_id
(
    order by id asc
    | serialize row_num = row_number()
)
| extend 
    Trolley_Start = iff(row_num == 1, Begin_Picking, Trolley_Start),
    Trolley_End = iff(row_num == 2, End_Picking, Trolley_End)
| project cart_id, id, Associate_1, Associate_2, Trolley_Start, Begin_Picking, End_Picking, Trolley_End
| order by cart_id, id asc 
| order by id asc, Trolley_End asc
| extend prev_Trolley_End = prev(Trolley_End), prev_id = prev(id)
| extend associate_downtime = iff(prev_id == id,datetime_diff('millisecond',prev_Trolley_End,Trolley_Start)/60000.0,real(null)), trolley_order = row_number(1,prev_id <> id)

Can someone please help!!!

Thiết kế website giá rẻ

Danh mục

KQL downtime calculation