Find Min/Max within date range

[MrSmartyPants]

I am looking for the most efficient way to accomplish the following ...

I have two tables.  One that stores time ranges indicating production downtime, and one that stores incremental units produced as recorded at various timestamps.

Downtime:

Start Time	End Time	Duration (Mins)
5/26/2019 8:17:44	5/26/2019 8:18:44	1
5/26/2019 9:13:04	5/26/2019 9:36:24	23.3
5/26/2019 9:46:44	5/26/2019 9:50:44	4
5/26/2019 9:52:04	5/26/2019 9:52:59	0.9
5/26/2019 11:13:35	5/26/2019 11:16:25	2.8

Production:

time	value
5/26/2019 8:17:35	1104
5/26/2019 8:17:43	1196
5/26/2019 8:17:51	1288
5/26/2019 8:17:57	1380
5/26/2019 8:18:03	1472
5/26/2019 8:18:11	1564
5/26/2019 8:18:17	1656
5/26/2019 8:18:23	1748
5/26/2019 8:18:31	1840
5/26/2019 8:18:37	1932
5/26/2019 8:18:43	2024
5/26/2019 8:18:51	2116

For each record in the Downtime table, I want to capture the min and max values of the records in the Production table where the Production timestamp falls within the Start Time and End Time. This is so I can determine the number of units produced during the downtime period (Max Units - Min Units)

Output:

Start Time	End Time	Duration (Mins)	Min Units	Max Units	Units Produced
5/26/2019 8:17:44	5/26/2019 8:18:44	1	1288	2024	736

For reference, the Downtime table would typically have about 100 entries per day and the Production table would have 40-50,000 records per day.  I can see this workflow being run for date ranges up to a full year, so there could be a massive amount of records to process.

I am also open to other suggestions on how to achieve the same results.

Thanks in advance

[Kenda]

Hi @MrSmartyPants 

The first idea that comes to mind to solve this would be to first use an Append Fields tool then a Filter to keep only the records where the production time falls within each of the start/end times. From there, you just have to Summarize and use a Formula to calculate the difference. With so many records in the production table, though, the Append Fields tool may take a while. Try this out and let us know how it works for you.

TIP: Make sure you change the drop down at the bottom of the Append Fields tool to "Allow All Appends."

[ponraj]

I would solve this problem using iteration macro as there are some limitation with appending data using append tool.   You just need update max of number of iteration field of interface designer with count of records there in production table for macro everytime when you run this workflow 

[estherb47 · Accepted solution]

Hi @MrSmartyPants 

I find that the Generate Rows tool can be less frustrating than Append, especially when you're appending so many records. Perhaps it's faster too (I haven't tested)

You can use Generate Rows to create entries for all of the times between the ranges in your Downtime table. Then, join to the Production table, Summarize for Max and Min, and Formula to calculate the difference.

Let me know if this helps!

Cheers,

Esther

[MrSmartyPants]

I tested all three suggested solutions with a larger data set.  I used a month of data. 

The append fields solution worked and took a little over a minute to run and required upwards of 20GB of memory while running. 

The iterative macro solution might have worked, but after 10 minutes and 25,000 iterations (out of 147,000 production records) it was pretty clear that it wasn't going to be the most efficient.

The Generate Rows solution provided the same results as the Append Fields solution but was able to crunch all the data in less than 10 seconds with no noticeable memory spike!!

Thanks for your help everyone!