moving average question - 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

SQL Server Development (2000)

moving average question

Author

Topic

spc2941
Starting Member

3 Posts

Posted - 2007-09-22 : 01:10:00

hello,

I'm new to SQL, and I'm having trouble with calculating the moving average for each observation within a time period and group. My challenge is that I need to get the average for each record, but the values (in my example "sales) are not in the same observation rather they are in different rows within the dataset. I plan on sorting the dataset first based on my groupings, then somehow calculate the moving average for each group within my time period of 90 days.

My table looks something like this:

Car_model make sold_dt sales moving_avg_sales
ford taurus 9-3-99 33,211 33,211
honda civic 9-3-92 23,222 23,222
honda civic 2-13-94 10,000 33,222
honda civic 7-31-94 12,000 45,222
ford taurus 5-3-02 5,000 38,211

so for each car sold, I will have a moving_avg_sales based on the each record; sold_date's rolling 90 previous days grouped by car_model, make, and sales dt.

I'm having trouble knowing how to get the moving average for sales since I need the value, but the sold dates to determine which observations to accumulate are on different rows. Can anyone help based on this sample data? thanks

so far, I have the following, which i know is wrong. I can't even complete this part. Sorry for being stupid. I'm new to this.

create table x as
select car_model, make, sold_dt, sales, mean(sales) as moving_avg_sales
from xx
where ???? between sold_dt and sold_dt-90days
group by car_model, make;

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 02:12:20

[code]
SELECT car_model, make, sold_dt, sales,
[moving_avg_sales] =
(
SELECT AVG(CS2.SalesPrice)
FROM CarSales AS CS2
WHERE CS2.car_model = CS1.car_model
AND CS2.make = CS1.make
AND CS2.sold_dt >= DATEADD(Day, DATEDIFF(Day, 0, CS1.sold_dt)-90, 0)
AND CS2.sold_dt <= CS1.sold_dt
)
FROM CarSales AS CS1
[/code]
Kristen

spc2941
Starting Member

3 Posts

Posted - 2007-09-23 : 18:28:05

thanks.. stupid question, but I see that you have CS1 and CS2 eventhough they are referencing the same dataset. why is this necessary?

spc2941
Starting Member

3 Posts

Posted - 2007-09-24 : 00:50:53

i'm trying to learn, so sorry for the stupid questions, but why is the dateadd needed?

Kristen
Test

22859 Posts

Posted - 2007-09-24 : 02:22:46

"[]I see that you have CS1 and CS2 eventhough they are referencing the same dataset."

No, two different datasets, but using the same table. Each is given an ALIAS name (CS1 / CS2) so that the columns in each data set can be explicitly referenced unambiguously.

"why is the dateadd needed?[/i]"

I assumed that your [sold_dt] might contain a time, and that "moving average within period of 90 days" would mean from midnight the night before until midnight following, rather than to exactly the time of the transaction. the DATEADD / DATEDIFF thing, whilst ghastly to read!, is the most efficient way to strip off the time; its subtracting 90 days at the same time.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources