Results 1 to 4 of 4

Thread: Find missing rows and average

  1. #1
    Join Date
    Jun 2011
    Posts
    4

    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!

  2. #2
    Join Date
    Jun 2011
    Posts
    4
    anyone please?

  3. #3
    Join Date
    Feb 2011
    Location
    Melbourne, Australia
    Posts
    13
    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

  4. #4
    Join Date
    Jun 2011
    Posts
    4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •