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?

Member Avatar

LastMitch

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

commented: Thanks LastMitch +2

Thanks a lot for the link LastMitch, it helps a lot.