Find missing rows and average

[gg_coups]

Hi All, I need some help.

I have a table that contains the store#, net(income) and time (of net income). The job that populates this table runs every hour. The problem is that when there is an outage on the store, the job will not run so there will be missing hours.

I need to create a sql statement or a stored procedure that will create new rows for the missing hours and get an estimate of how much the net income for that hour should have been.

Here is an example of my table:

store# Net Time
10 74.77 2011-06-13 10:00:00.000
10 1788.07 2011-06-13 11:00:00.000
10 8066.17 2011-06-13 12:00:00.000
10 52231.36 2011-06-13 17:00:00.000

In the above table, there are no entries for store 10 between 13:00 to 16:00. I need to be able to insert a row for those hours as well as their corresponding net by getting the average of the net of 12:00 and 17:00. In this case, the values should be:

10 16899.21 2010-06-19 13:00:00.000
10 25732.25 2010-06-19 14:00:00.000
10 34565.29 2010-06-19 15:00:00.000
10 43398.33 2010-06-19 16:00:00.000

Any kind of help will be very much appreciated!

[rmiao]

Dup post: http://forums.databasejournal.com/sh...ad.php?t=52675