Difference between two Start Dates

[jorditorras]

Dear Gurús,

I’m struggling with a case that might be easy but I haven’t found out how to manage it…

I have a table like this:

Order	Stop_DateTime
121	18/05/2021 1:03:05
121	18/05/2021 1:03:32
121	18/05/2021 1:04:21
121	18/05/2021 1:07:11
121	18/05/2021 1:08:23
126	18/05/2021 1:08:34
126	18/05/2021 1:10:23
126	18/05/2021 1:11:01
129	18/05/2021 1:11:33
129	18/05/2021 1:12:43
129	18/05/2021 1:12:46
129	18/05/2021 1:13:21
129	18/05/2021 1:13:29
131	18/05/2021 1:13:31
131	18/05/2021 1:15:48

Where I have Orders and Stop DateTime. I need to know how long is the stop for each Order. For that I have to take the difference between the first value of each order and the first value of the next Order. So, I need to calculate that Time Difference or being able to add some fields in the table like this:

Order	Stop_DateTime	Start Stop	End Stop
121	18/05/2021 1:03:05	18/05/2021 1:03:05	18/05/2021 1:08:34
121	18/05/2021 1:03:32	18/05/2021 1:03:05	18/05/2021 1:08:34
121	18/05/2021 1:04:21	18/05/2021 1:03:05	18/05/2021 1:08:34
121	18/05/2021 1:07:11	18/05/2021 1:03:05	18/05/2021 1:08:34
121	18/05/2021 1:08:23	18/05/2021 1:03:05	18/05/2021 1:08:34
126	18/05/2021 1:08:34	18/05/2021 1:08:34	18/05/2021 1:11:33
126	18/05/2021 1:10:23	18/05/2021 1:08:34	18/05/2021 1:11:33
126	18/05/2021 1:11:01	18/05/2021 1:08:34	18/05/2021 1:11:33
129	18/05/2021 1:11:33	18/05/2021 1:11:33	18/05/2021 1:13:31
129	18/05/2021 1:12:43	18/05/2021 1:11:33	18/05/2021 1:13:31
129	18/05/2021 1:12:46	18/05/2021 1:11:33	18/05/2021 1:13:31
129	18/05/2021 1:13:21	18/05/2021 1:11:33	18/05/2021 1:13:31
129	18/05/2021 1:13:29	18/05/2021 1:11:33	18/05/2021 1:13:31
131	18/05/2021 1:13:31	18/05/2021 1:13:31
131	18/05/2021 1:15:48	18/05/2021 1:13:31

Any idea how to calculate this?

Thanks in advance!!

Regards,

Jordi

[Lauri]

Table:
Load
Order,
Min(Stop_DateTime) as "Start Stop",
Max(Stop_DateTime) as "End Stop"
From [...]
Group By Order;

Left Join
Load
Order,
Stop_DateTime
From [...];

[jorditorras]

Hi, 

Thanks for your reply, but this is not exactly the requirement that I have... Because the "End Stop" has to be the first DateTime of the next Order and not the Max DateTime of each order...