SQL Server Forums
Profile | Register | 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
 New Topic  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
17642 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  
 New 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.08 seconds. Powered By: Snitz Forums 2000