Very new to T-SQL and SQL Server but already have come across a complex problem even my more experienced colleagues don't know how to solve.
Basically, we have meter readings taken from devices every day over the internet, and from this we can determine the daily volume. But occasionally the reading data is 'spotty' and some devices will be offline for a day or sometimes many days at a time. When the device comes back online we can tell what volume has been done across those days, but obviously not the amount done on each individual day.
In order to plug these gaps, I want to fill in the missing days with the average per day. A chart will end up having a straight line for a single device, but I'd rather that than have it drop off altogether. Charting will generally display the sum of many hundreds of devices anyway so the averaging of a few sporadically won't be too noticeable.
My problem is how! I have a table of every device with every date, and where we know the volume it's already filled in. But how do I fill in the nulls?
This screenshot of a mock-up might explain all of this a lot better:
What do I have to run against Table A (which determines the gaps between readings, and determines the average per day when the gaps in readings is multiple days) to populate Table B (which displays the volume per day)?
Many thanks in advance for any assistance on this.