Three month Average

Question

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Something like this?

;WITH id_CTE AS (

SELECT name, status, fordate, a,

ROW_NUMBER() OVER (ORDER BY fordate ASC) AS id

FROM #data),

work_CTE AS (

SELECT name, status, fordate, a, id

FROM id_CTE

WHERE id = 1

UNION ALL

SELECT a.name, a.status, a.fordate, (a.a+b.a+c.a)/3.0 AS a, a.id

FROM id_CTE a

INNER JOIN id_CTE b ON a.id-1 = b.id

INNER JOIN id_CTE c ON a.id-2 = c.id)

SELECT name, status, fordate, a AS average FROM work_CTE

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

taybre SSC Eights! Points: 916 More actions · Answer 2

@ skcadavre

That worked like a dream.

Thank you

:-):-)

taybre

HTH :-):-)
taybre

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/