0

Hi All,

I have a table that holds monthly data in it. I need to average that data into Quarterly and Annual data but I'm not sure how to go about that. For annual I tried something like:

SELECT     TOP (100) PERCENT b.vSeries_Type as [Description],b.vSeries_Number as [CANSIM], Year(a.IPI_Ref_Date)as [Year], avg(a.IPI_Value) as [IPI]
FROM         dbo.tblIPI AS a INNER JOIN
                      dbo.tblVSeriesList AS b ON a.IPIvSeries_ID = b.vSeries_ID AND (b.vSeries_Number = 'v53384920' OR
                      b.vSeries_Number = 'v53384879' OR
                      b.vSeries_Number = 'v53433827' OR)
group by b.vSeries_Type,b.vSeries_Number,a.IPI_Ref_Date
ORDER BY b.vSeries_Number asc

However the output is definitely not Annual, I don't know exactly what the calculation did. Can someone point me in the right direction?

2
Contributors
2
Replies
22
Views
4 Years
Discussion Span
Last Post by Stuugie
1

I have a table that holds monthly data in it. I need to average that data into Quarterly and Annual data but I'm not sure how to go about that.

I'm not sure how to approach with the query (it's because of the numbers and join columns.):

SELECT TOP (100) PERCENT 
b.vSeries_Type AS [Description], b.vSeries_Number AS [CANSIM], 
Year(a.IPI_Ref_Date) AS [Year], avg(a.IPI_Value) AS [IPI] FROM dbo.tblIPI 
AS a INNER JOIN dbo.tblVSeriesList 
AS b ON a.IPIvSeries_ID = b.vSeries_ID 
AND (b.vSeries_Number = 'v53384920' OR b.vSeries_Number = 'v53384879' OR b.vSeries_Number = 'v53433827' OR)
GROUP BY b.vSeries_Type, b.vSeries_Number, a.IPI_Ref_Date
ORDER BY b.vSeries_Number asc

However the output is definitely not Annual, I don't know exactly what the calculation did. Can someone point me in the right direction?

Read this tutorial and used the query from this link:

http://www.sqlservercurry.com/2011/04/sql-server-first-and-last-day-of-year.html

Edited by LastMitch: grammer

Votes + Comments
Thanks LastMitch
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.