Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Running Average based on Volume

Author  Topic 

JustaHustla
Starting Member

5 Posts

Posted - 2007-11-15 : 12:28:52
I'm trying to get a running average of a Value. The trick is, I don't want the running average to be time based; I want it to be volume based.

I was able to do this based on a 60 record rolling average with the following code:

MyTable:
Datetime - Value
12:00 - 1.0
12:01 - 1.1
12:02 - 1.0
12:03 - 1.2
12:04 - 0.9
12:05 - 1.1

select Datetime,
[VALUE] + (select sum([VALUE]) from MyTable where datetime in
(select top 59 datetime from MyTable where datetime < t.datetime order by datetime desc)
having count(datetime) > 58) as RollingAvg
from MyTable t
where (select count(datetime) from MyTable where datetime < t.datetime) > 59
order by datetime

I would like to do the same thing, by somehow replacing the 60 records in each sample with a number of records where the sum of my value = x.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 18:47:32
[code]
mydate myvalue RunAvg
1900-01-01 12:00:00.000 1.000000 1.000000
1900-01-01 12:01:00.000 1.100000 1.050000
1900-01-01 12:02:00.000 1.000000 1.033333
1900-01-01 12:03:00.000 1.200000 1.075000
1900-01-01 12:04:00.000 .900000 1.040000
1900-01-01 12:05:00.000 1.000000 1.033333

[/code]

FROM
[code]
Create Table #Mytable(mydate datetime not null,
myvalue numeric(10,6) not null)


Insert Into #MyTable(mydate, myvalue)
Select '1/1/1900 12:00 PM', 1.0 UNION ALL
Select '1/1/1900 12:01 PM', 1.1 UNION ALL
Select '1/1/1900 12:02 PM', 1.0 UNION ALL
Select '1/1/1900 12:03 PM', 1.2 UNION ALL
Select '1/1/1900 12:04 PM', 0.9 UNION ALL
Select '1/1/1900 12:05 PM', 1.0


Select a.mydate,
a.myvalue,
(Select avg(myvalue) as RA
FROM #mytable b
WHERE b.mydate<= a.mydate) as RunAvg

FROM #mytable a
Order by mydate asc

Drop Table #mytable
[/code]


HTH




Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-15 : 18:54:24
Sorry, should have finished this off to show you the variable part..


Create proc pRunAvg(@limit int not null)
AS

Declare @SQL varchar(8000)

Select @SQL = '


Select TOP ' + @limit +
' a.mydate,
a.myvalue,
(Select avg(myvalue) as RA
FROM #mytable b
WHERE b.mydate<= a.mydate) as RunAvg
FROM mytable a
Order by mydate asc '

Exec (@SQL)


would be an example of how to do this using dynamic SQL to return TOP @limit (where @limit is the number passed to the proc)





Poor planning on your part does not constitute an emergency on my part.

Go to Top of Page

JustaHustla
Starting Member

5 Posts

Posted - 2007-11-15 : 21:25:05
I appreciate the response but that's not what I'm looking for here. My apologies for not making myself clear. I'll try to add more detail.

Given the Table:

Create Table #Mytable(mydate datetime not null,
myvalue numeric(10,6) not null,
mysize numeric(10,6) not null)

Insert Into #MyTable(mydate, myvalue, mysize)
Select '1/1/1900 12:00 PM', 1.0, 5 UNION ALL
Select '1/1/1900 12:01 PM', 1.1, 5 UNION ALL
Select '1/1/1900 12:02 PM', 1.0, 4 UNION ALL
Select '1/1/1900 12:03 PM', 1.2, 6 UNION ALL
Select '1/1/1900 12:04 PM', 0.9, 5 UNION ALL
Select '1/1/1900 12:05 PM', 1.0, 6

I would like to get back a rolling average of myvalue where the sample size
includes the current datapoint and however many previous data points is takes
to get sum(mysize) >= 10.

so the result I'm looking for is

mydate RunAvg Sum(MySize)
1900-01-01 12:00:00.000 NULL 5
1900-01-01 12:01:00.000 1.050000 10
1900-01-01 12:02:00.000 1.033333 14
1900-01-01 12:03:00.000 1.100000 10
1900-01-01 12:04:00.000 1.050000 11
1900-01-01 12:05:00.000 1.025000 11
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 22:02:20
can you show example how to calc the RunAvg for these records ?


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

JustaHustla
Starting Member

5 Posts

Posted - 2007-11-16 : 07:54:01
now with descriptions:

mydate RunAvg Sum(MySize) (Records used to calculate average)
1900-01-01 12:00:00.000 NULL 5 12:00 --no average since we don't yet have a sum >= 10
1900-01-01 12:01:00.000 1.050000 10 12:01 + 12:00 --sum of mysize = 10 so we average 2 samples
1900-01-01 12:02:00.000 1.033333 14 12:02 + 12:01 + 12:00 --** here we need 3 samples to get a sum >= 10
1900-01-01 12:03:00.000 1.100000 10 12:03 + 12:02 --2 samples here is enough
1900-01-01 12:04:00.000 1.050000 11 12:04 + 12:03 --2 samples here is enough
1900-01-01 12:05:00.000 1.025000 11 12:05 + 12:04 --2 samples here is enough
Go to Top of Page
   

- Advertisement -