Max Multiple Criteria in Load Script

[Yorick_Stevens]

Dear All,

I have the following data set:

ID	OfficeID	Date	Time	TimeStamp	TimeDiffNext	Max in 36h
1	1	3/03/2020	9:28:35	3/03/2020 9:28	5:37:59	17:21:11
1	1	3/03/2020	15:06:34	3/03/2020 15:06	17:21:11	17:21:11
1	1	4/03/2020	8:27:45	4/03/2020 8:27	9:09:16	38:10:41
1	1	4/03/2020	17:37:01	4/03/2020 17:37	0:52:32	38:10:41
1	1	4/03/2020	18:29:33	4/03/2020 18:29	38:10:41	38:10:41
1	2	6/03/2020	8:40:14	6/03/2020 8:40	0:05:14	64:11:01
1	2	6/03/2020	8:45:28	6/03/2020 8:45	3:54:57	64:11:01
1	2	6/03/2020	12:40:25	6/03/2020 12:40	4:29:09	64:11:01
1	2	6/03/2020	17:09:34	6/03/2020 17:09	64:11:01	64:11:01
1	1	9/03/2020	9:20:35	9/03/2020 9:20	0:07:17	21:20:16
1	1	9/03/2020	9:27:52	9/03/2020 9:27	2:45:01	21:20:16
1	1	9/03/2020	12:12:53	9/03/2020 12:12	21:20:16	21:20:16
1	1	10/03/2020	9:33:09	10/03/2020 9:33	0:00:02	37:28:28
1	1	10/03/2020	9:33:11	10/03/2020 9:33	3:48:30	37:28:28
1	1	10/03/2020	13:21:41	10/03/2020 13:21	5:40:20	37:28:28
1	1	10/03/2020	19:02:01	10/03/2020 19:02	37:28:28	37:28:28

I would like to add the blue column which is the maximum of the column "TimeDiffNext"
under the following conditions for the range:
- The same EmployeeID
- The same OfficeID
- TimeStamp Field >= Current TimeStamp -18h
- TimeStamp Field <= Current TimeStamp +18h

Could you please help,
Kind regards,
Yorick Stevens

[lorenzoconforti]

Hi

Trying to understand what you are trying to achieve. How is this the max of any period? 38:10:41

How many rows you need to go through? How big is your table?

[Yorick_Stevens]

Thanks for the swift reply,

Apparently their is an error in my formatting i'm very sorry.
I've just adapted it. The table varies between 200k and 500k of records.

Kind Regards,
Yorick Stevens

[lorenzoconforti · Accepted solution]

See attached; let me know if you want me to explain how it all works