ken_rgr
asked on
Calculating Quarterly average from monthly data
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
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
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
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
ASKER
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 ???
I have 10 years of monthly data(120 rows).
That means it should return 30 rows.
Any better solution ???
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
SELECT SUM(UrColumn) Total, DATENAME (qq, urDAteColumn ) as Quarter , YEAR (urDateColumn ) as [Year]
FROM urTable
GROUP BY DATENAME (qq, urDAteColumn ) ,YEAR (urDateColumn )
FROM urTable
GROUP BY DATENAME (qq, urDAteColumn ) ,YEAR (urDateColumn )
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)
SELECT Datepart(qq, YourDate) Quarter, DatePart(yy, YourDate) YearNr, AVG(monthlyValue)
FROM YourTable
GROUP BY Datepart(qq, YourDate), DatePart(yy, YourDate)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The more accurate way would be to calculate half year and quarterly data from daily data because it reflects number of business days.
ASKER
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
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
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?
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?
SELECT SUM(UrColumn) Total, DATENAME (qq, urDAteColumn ) as Quarter
FROM urTable
GROUP BY DATENAME (qq, urDAteColumn )