Well production and workover data joins and calculating pump runlives
I have 2 set of data. The first has a number of different wells with workover dates when a pump is changes and its associated capacity. The second data set is the production dates and how many hours the well was online for. ("Pump information" & "Production online" tabs in excel sheet attached)
I am trying to join these 2 datasets so that I can calculate the number of days between each pump change but only for when the well was online greater than 0 hours.
Join "Pump information" to "Production online", with Production online being the parent dataset.
eg. Well 1 has a pump change on 31/July/2012 with a capacity of 2.8
The calculated column for online days should be
if (current date [Online hours] > 0,
Then Previous online day + 1,
Else = Previous online day
This also need to be OVER the [Well Name]
I then need to calculate what the number of online days associated with the pump change on 31/July/2012 until the next pump change.
Example The number of well online days between the pump change on 31/July/2012 and the next pump change on 8/March/2014 is 421 days.
Thanks for your help.