All,
I have a specific problem to solve, and, although I succefully solved it on a tiny subset of original data manually, I won't be able to do this to the whole huge set of data.
So, here's the problem. I have two tables.
First table - some measurements done on some set of tools
| Tool | Date | Measurement |
A001 | 1/1/2017 | 0.005 |
| B006 | 2/2/2017 | 0.003 |
Second table - history of some parts replacement on the same tools:
| Tool | Date | Part S/N |
| A001 | 1/1/2016 | 1234567 |
| B006 | 2/2/2016 | 0987654 |
| A001 | 12/29/2016 | 1234568 |
What I would like to see, how measurements for each tool depend on the installed part age. What I need as a resultant table is the same table with measurements, only with few added columns:
| Tool | Date | Measurement | Installed part | Installation date | Age of part |
A001 | 1/1/2017 | 0.005 | 1234568 | 12/29/2016 | 4 days |
| B006 | 2/2/2017 | 0.003 | 0987654 | 12/29/2016 | 365 days |
Basically, I did this manually for one tool - just created column and filled it with the ear;liest replacement date. Then moved down the measurement table until the date of next install - and from there filled the same column to the end with new install date, then moved further.... Tedious.
I need to somehow automate that, but I can't even start wrapping my head around it.
So, for each line in measurement table, I need to find a line in parts replacement table, that has matched tool, and has install date earlier than the date for line under consideration. Then I would need to take that serial number, that date and put it in measurement table. Then it's easy to calculate part age. Some parts install dates are so long ago that they are not covered by both these tables, so I might have lots of missing values.
Could somebody help me with methodology and maybe a little bit with starting the script?
Thanks a lot!
