Difference between two Start Dates

jorditorras — Tue, 18 May 2021 15:59:23 GMT

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

Re: Difference between two Start Dates

Lauri — Tue, 18 May 2021 19:59:29 GMT

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 [...];

Re: Difference between two Start Dates

jorditorras — Wed, 19 May 2021 06:46:29 GMT

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...

topic Difference between two Start Dates in App Development

Difference between two Start Dates

Re: Difference between two Start Dates

Re: Difference between two Start Dates