I would like to calculate monthly meter values from irregular readings. My data looks like this:
| Date | Meter Reading |
--------------------------------
| 2018-01-01 | 1.200 |
| 2018-03-22 | 2.800 |
| 2018-05-19 | 4.600 |
As you can see, the entries can happen any date and they are not on the first and / or last day of a month. For a monthly view I therefore need to interpolate the data and distribute it equally across the months between two readings.
If you interpolate the difference in the measurements and divide them with the days passed between them, you get two "daily" estimations of 30,19 and 31,03. This again multiplied with the number of days in each month results in (current day is assumed to be the 2018-05-19):
| Month | Production |
---------------------------
| 2018-01 | 935,89 |
| 2018-02 | 845,32 |
| 2018-03 | 943,45 |
| 2018-04 | 930,90 |
| 2018-05 | 589,57 |
Is there a way to perform this on-the-fly query using SQL?
Thanks!
