Rolling Averages - SQL Server Forums

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)

Rolling Averages

Author

Topic

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-07-14 : 04:06:03

Hi All

I have a table with dates and price information. i need to find the rolling monthly price average. I can work out the average but i am having trouble extracting each months price information. can this be done in one query or not? I hope this makes sense and someone can help.




Table 


GoodID  Price   Date

1	23.5	2003-07-13 00:00:00.000	
1	23.5	2003-07-12 00:00:00.000	
1	23.5	2003-07-11 00:00:00.000	
1	23.5	2003-07-10 00:00:00.000	
1	23.5	2003-07-09 00:00:00.000	
1	21.5	2003-06-08 00:00:00.000	
1	22.5	2003-05-07 00:00:00.000	
1	23.5	2003-04-06 00:00:00.000

ect..

basically i would like a result set something like this




GoodID   Average    Date
1        23.5       07-2003
1        21.5       06-2003  
1        22.5       05-2003

Thanxs

OMB

Edited by - omb on 07/14/2003 04:19:37

mr_mist
Grunnio

1870 Posts

Posted - 2003-07-14 : 04:16:58

You should be able to do

SELECT
goodid, avg (price), month ([date]), year (date)
FROM
yourtable
GROUP BY
goodid, month ([date]), year (date)

However, this would make poor or non-existant use of indexes. You might be better off creating another column that signifies the month and year, depending on your data.

Someone with a better knowledge of dates may also suggest an improved method.

-------
Moo. :)

OMB
Yak Posting Veteran

88 Posts

Posted - 2003-07-14 : 04:35:14

Thanxs mr_mist

I know its not the most efficient but this is a one off so it does not really matter, however if someone does have a better solution i would be very interested.

OMB

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 10:29:11

quote:

this is a one off so it does not really matter

Biggest lie in the industry....

Never happen again...

It's only a 1 time thing

Don't have to code it correctly, the system won't be around that long

The data coming from the user excel spreadsheet will be sanitized, constraints aren't required...

What are the others????

Brett

8-)

uberman
Posting Yak Master

159 Posts

Posted - 2003-07-14 : 12:30:01

They'll never notice...

Tea from a machine tastes fine...

Don't worry men, they're out of range...

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 13:37:02

The Checks in the mail..

Since it's going to have to scan anyway...how can you enable multiple threads to kick off to add some degree of parallelism?

Would the following do that? IS it possible?



SELECT goodid, avg (price) AS Avg_Price, Month_Group, Year_Group 
FROM (
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 1 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 2 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 3 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 4
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 5
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 6
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 7
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 8 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 9 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 10 
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 11
UNION ALL 
SELECT goodid, price, MONTH([date]) As Month_Group, YEAR([date]) AS Year_Group FROM yourtable MONTH([DATE]) = 12
) AS XXX
GROUP BY goodid, Month_Group, Year_Group

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-14 : 16:18:47

quote:
Since it's going to have to scan anyway...how can you enable multiple threads to kick off to add some degree of parallelism?

The optimizer will handle this, provided you actually have multiple processors and the benefit of parallelization outweighs the extra overhead of setting it up.

Jonathan
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-14 : 16:35:06

the bottleneck is usually I/O anyway ....

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 16:44:12

Cool,

In DB2 OS/390 you have to set up a lot more stuff, partitioned indexes/ tablespaces, SET DEGREE ANY...yada yada yada

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-14 : 16:54:40

quote:

the bottleneck is usually I/O anyway ....

- Jeff

So, is that configurable? Can you throw more hardware at the i/o?

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-14 : 17:02:43

No. SQL 2K determines all the necessary parameters for you, such as max # of asynchronous I/O's sent to the I/O subsystem. You can make physical layout choices such as clustered indexes or filegroup placement, however.

You can certainly "throw more hardware" at I/O - far and away it is the most scalable of the four basic performance components (CPU,RAM,network,I/O). These days you can build a RAID array to the sky.

Jonathan
{0}

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources