Averaging with the values in different ways-HELP!!

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

General SQL Server Forums

New to SQL Server Programming

Averaging with the values in different ways-HELP!!

Author

Topic

ultiron
Starting Member

2 Posts

Posted - 2010-11-04 : 12:12:54

Hey everyone,

Ive made a table containing the shares prices of some companies on various dates. I need to create a table where I can list the average share price for each comany for the past

a) week
b) year

These need to be in the same table (i.e 3 rows , company_name,average_over_a_week, average_over_a_year)

I can do this fine using separate tables but am finding it diffcult to produce the same result in just one table.

can anyone help??

thanks in advance

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-04 : 17:37:10

The more general solution is to build a table with reporting periods,
somethimg like this skeleton

CREATE TABLE ReportPeriods
(period_name CHAR(n) NOT NULL PRIMARY KEY,
period_start_date DATE NOT NULL,
period_end_date DATE NOT NULL,
CHECK (period_start_date <= period_end_date),
etc.);

You can get weeks, quarters, fiscal calendars, odd-ball religious
holidays, etc. The code will be portable and can use indexes in the
joins.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-11-04 : 18:14:44

Another way to do it (proof of concept, change names as needed):

declare @t table(company varchar(10), date datetime, shareprice money)

insert @t values('ABC', '1/1/2010', 2.75)
insert @t values('ABC', '3/6/2010', 3.90)
insert @t values('ABC', '5/8/2010', 4.85)
insert @t values('ABC', '7/10/2010', 7.45)
insert @t values('ABC', '9/15/2010', 3.73)
insert @t values('ABC', '10/30/2010', 5.75)
insert @t values('ABC', '11/4/2010', 8.75)

insert @t values('Acme', '1/1/2010', 2.475)
insert @t values('Acme', '4/6/2010', 3.755)
insert @t values('Acme', '4/8/2010', 8.765)
insert @t values('Acme', '5/10/2010', 3.475)
insert @t values('Acme', '7/15/2010', 6.75)
insert @t values('Acme', '9/18/2010', 11.65)
insert @t values('Acme', '10/26/2010', 8.75)

select company, 
avg(case when date between getdate()-7 and getdate() then shareprice end) weekly_avg,
avg(case when year(date)=year(getdate()) then shareprice end) yearly_avg
from @t
group by company

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources