Difference between 2 records

[Laynlowz]

Hello

I am looking for some help to find the proper function to do a calculation between 2 records. Basically we have gaps In our production and want to calculate a total per user to see their ideal time between orders. Any help would be great!

Example would be

Activity       start time        end time           difference
Ambient        6:30am          7:00 am     
Anchor           7:00 am         7:01 am
Ambient         7:15 am         7:30 am           14 minutes
Anchor           7:30 am          7:31 am
Ambient         7:50 am          8:00 am          19 minutes
Anchor           8:00 am           8:01 am
Ambient         8:15 am           8:45 am         14 minutes

[MFGF]

Hello

I am looking for some help to find the proper function to do a calculation between 2 records. Basically we have gaps In our production and want to calculate a total per user to see their ideal time between orders. Any help would be great!

Example would be

Activity       start time        end time           difference
Ambient        6:30am          7:00 am     
Anchor           7:00 am         7:01 am
Ambient         7:15 am         7:30 am           14 minutes
Anchor           7:30 am          7:31 am
Ambient         7:50 am          8:00 am          19 minutes
Anchor           8:00 am           8:01 am
Ambient         8:15 am           8:45 am         14 minutes

Hi,

I think you're going to need two queries for this - one filtered for the Anchor rows and the other for the Ambient rows. You can then join them so that the times are on the same row, and you'll then be able to calculate the time difference.

Cheers!

MF.

[BigChris]

Even with two queries, I can't see how you'd do it. In the example data given, looking at the last two rows, Laynlowz is trying to calculate the difference between the end tie of the Anchor row (08:01) and the start time of the Anchor row (08:15), i.e. 14 minutes. I don't see how you'd join those two bits of data.

The first two rows of data is a bit of a red herring, as there's no Anchor row for the Ambient entry on row 2.

[Laynlowz]

so I was able to figure this out by making 2 queries and using the rank function again the date field. in query 1 i just ranked the dates and in query 2 i ranked the dates +1 and then joined on the rank ID to bring the second row start time to line up with the first row and then I used the _minutes_between function to get the duration.

my new issue is we have break times during our day but due to some employees not working full shifts (holiday/sick, etc) I can't just do (-70 minutes) so i basically need to account for the breaks as the user meets those times. how would I achieve this?

i have the following data
ambient 10:07 to 10:08
anchor 10:08 to 10:54  gap of 46 minutes   (our break is 10:15 to 10:50 so i need to deduct this time)