Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Calculating Quarterly average from monthly data

Posted on 2009-05-08
10
Medium Priority
?
970 Views
Last Modified: 2012-05-06
Hi
In my application I need to calculate Quarterly average for Dow 30..
I have montly data available for dow30.
I need to generate quarterly and half yearly data from monthly data.
I need to create a stored Proc for this.
I am using Sql server 2005.
Any help will appreciated.
Thanks
0
Comment
Question by:ken_rgr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
10 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24336835


SELECT SUM(UrColumn) Total, DATENAME (qq, urDAteColumn ) as Quarter
FROM urTable
GROUP BY DATENAME (qq, urDAteColumn )
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24336929
How do you identify month in your data? Is a date or string?

As the rough estimation you may use following (suppose monthNr is numeric month number 1 - 12, YearNr is four digits Year number):

SELECT cast((monthNr-1)/3 AS int)+1 Quarter, YearNr, AVG(monthlyValue)
  FROM YourTable
 GROUP BY cast((monthNr-1)/3 AS int), YearNr

SELECT cast((monthNr-1)/6 AS int)+1 HalfNr, YearNr, AVG(monthlyValue)
  FROM YourTable
 GROUP BY cast((monthNr-1)/6 AS int), YearNr

0
 

Author Comment

by:ken_rgr
ID: 24336993
The above solution is returning just 4 rows of data.
I have 10 years of monthly data(120 rows).
 That means it should return 30 rows.
Any better solution ???
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 
LVL 43

Expert Comment

by:pcelba
ID: 24337023
The SP creation is also easy but you have to know how it should store/return results. If you just need above SELECTs in SP execute the following code (updated to your structures). You may create input parameters for date range in this SP etc.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE sp_quarterlyData 
 
AS
BEGIN
SET NOCOUNT ON;
 
SELECT cast((monthNr-1)/3 AS int)+1 Quarter, YearNr, AVG(monthlyValue)
  FROM YourTable
 GROUP BY cast((monthNr-1)/3 AS int), YearNr
 
END
GO

Open in new window

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 24337060
SELECT SUM(UrColumn) Total, DATENAME (qq, urDAteColumn ) as Quarter , YEAR (urDateColumn ) as [Year]
FROM urTable
GROUP BY DATENAME (qq, urDAteColumn ) ,YEAR (urDateColumn )
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24337071
My solution does not return just 4 rows.If you have date column then you may update it:

SELECT Datepart(qq, YourDate) Quarter, DatePart(yy, YourDate) YearNr, AVG(monthlyValue)
  FROM YourTable
 GROUP BY Datepart(qq, YourDate), DatePart(yy, YourDate)

0
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 24337105
Half year data:

SELECT cast((Datepart(mm, YourDate)-1)/6 AS int)+1 HalfNr, DatePart(yy, YourDate) YearNr, AVG(monthlyValue)
  FROM YourTable
 GROUP BY cast((Datepart(mm, YourDate)-1)/6 AS int), DatePart(yy, YourDate)

0
 
LVL 43

Expert Comment

by:pcelba
ID: 24337336
The more accurate way would be to calculate half year and quarterly data from daily data because it reflects number of business days.
0
 

Author Comment

by:ken_rgr
ID: 24337460
Hi guys

Thank You very much for your Immediate response.
I am overwhelmed.
I am getting Quarterly averages for all the indexes.
But my table has 2 indexes one is Dow and second one is S&P.
how to get different avarages for these 2 separately
0
 
LVL 43

Expert Comment

by:pcelba
ID: 24340515
The groupping will remain unchanged, you just have to add one more column for S&P average calculation:

SELECT Datepart(qq, YourDate) Quarter, DatePart(yy, YourDate) YearNr, AVG(monthlyDOWValue) DOWavg, AVG(monthlySaPValue) SaPavg
  FROM YourTable
 GROUP BY Datepart(qq, YourDate), DatePart(yy, YourDate)

BTW, why you are not interested in NASDAQ?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A frequently used term in Object-Oriented design is "SOLID" which is a mnemonic acronym that covers five principles of OO design.  These principles do not stand alone; there is interplay among them.  And they are not laws, merely princ…
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Learn how to set-up custom confirmation messages to users who complete your Wufoo form. Include inputs from fields in your form, webpage redirects, and more with Wufoo’s confirmation options.
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question