-
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!
-
-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|