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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Running Average based on Volume
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JustaHustla
Starting Member

5 Posts

Posted - 11/15/2007 :  12:28:52  Show Profile  Reply with Quote
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.

Edited by - JustaHustla on 11/15/2007 13:08:17

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/15/2007 :  18:47:32  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote

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



FROM

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



HTH




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

Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/15/2007 :  18:54:24  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.


Edited by - dataguru1971 on 11/15/2007 18:54:45
Go to Top of Page

JustaHustla
Starting Member

5 Posts

Posted - 11/15/2007 :  21:25:05  Show Profile  Reply with Quote
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

Edited by - JustaHustla on 11/15/2007 21:28:02
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17689 Posts

Posted - 11/15/2007 :  22:02:20  Show Profile  Reply with Quote
can you show example how to calc the RunAvg for these records ?


KH
Time is always against us

Go to Top of Page

JustaHustla
Starting Member

5 Posts

Posted - 11/16/2007 :  07:54:01  Show Profile  Reply with Quote
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

Edited by - JustaHustla on 11/16/2007 07:55:03
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000