Returning average values based on start and stop times from another table

NCSU_MTLRGST

I have a JMP query which currently returns batch start and stop times for a given process:

 

Batch	Start Time	Stop Time
1	1/1/2019 1:50 AM	1/1/2019 4:50 AM
2	1/1/2019 6:50 AM	1/1/2019 8:50 AM

 

I have a separate database which I can query that has sensor data:

 

Timestamp	Sensor
1/1/2019 12:50 AM	50
1/1/2019 1:50 AM	90
1/1/2019 2:50 AM	110
1/1/2019 3:50 AM	100
1/1/2019 4:50 AM	100
1/1/2019 5:50 AM	50
1/1/2019 6:50 AM	70
1/1/2019 7:50 AM	90
1/1/2019 8:50 AM	80
1/1/2019 9:50 AM	80

 

  What I would like to do is calculate an average value from a separate table based on the start and stop times, and join that average value to my original table:

 

Batch	Start Time	Stop Time	AVG Sensor
1	1/1/2019 1:50 AM	1/1/2019 4:50 AM	100
2	1/1/2019 6:50 AM	1/1/2019 8:50 AM	80

jules

Hi @NCSU_MTLRGST,

There are a few ways to go about this, but if you don't want to do any scripting you can accomplish it completely using JMP Query Builder, under Tables > JMP Query Builder. Here's a quick video on how to do that (it's less complicated to see it than write out the steps). I've also attached all the tables I created in the process. 

I hope this helps!

@jules 

SQLQuery1.jmp

timestamp table.jmp

batch table.jmp