Find missing rows and average

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!

anyone please?

Try the code below.
You will still need to do some special stuff to cater for opening hours (the code below will also insert records between17:00 and 9:00 the next day). But this should get you started, if you have more questions I will try to answer them.

A better way might be to use a Trigger, when a value gets inserted for a specific shop, this would check the database and include the estimat entries if needed. I'm happy to help you with this, just ask.

Code:

---

-- =============================================
-- Author:                Name
-- Create date:
-- Description:       
-- =============================================
CREATE PROCEDURE [dbo].[complete]
AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        DECLARE C_CURSOR CURSOR FOR
        select store,net,T  From stores order by store,T       

        DECLARE @store int
        DECLARE @prevStore int
        DECLARE @net float
        DECLARE @prevNet float
        DECLARE @T datetime
        DECLARE @prevT datetime
       
        Declare @hoursDiff int
        declare @estimateIncrease float
        declare @i int--loop
       
        OPEN C_CURSOR
        FETCH FROM C_CURSOR INTO @store, @net, @T
        while (@@FETCH_STATUS=0)
        begin
                if @prevStore = @store --only when looking at same store
                begin
                                set @hoursDiff=DATEDIFF(hour,@prevT,@T)
                                if(@hoursDiff>1)
                                        begin
                                                set @estimateIncrease = (@net-@prevNet)/@hoursDiff
                                                set @i=1
                                                while (@i<@hoursDiff)
                                                begin
                                                        INSERT INTO [TEST].[dbo].[Stores] ([store],[net],[T])
                                                        VALUES(@store,@prevNet+@i*@estimateIncrease,DATEADD(hour,@i,@prevT))
                                                        set @i=@i+1
                                                end
                                        end
                end               
                set @prevStore=@store
                set @prevNet=@net
                set @prevT=@T
                FETCH FROM C_CURSOR INTO @store, @net, @T
        end
        close C_CURSOR
        DEALLOCATE C_CURSOR
END

---

Hi Gil! Thank you for your response... I really appreciate it.

Our stores are only open from 10am to 5pm so I only want the missing hours on that timeframe per day. I tried putting a between clause in the cursor but it didn't work... i hope you can help me with this too.