last five year avergae for same date

Question

Hi
I need help to create one column which calculate five year average and put into front of current month date.

example:-

Year Month Date Value Avg.

2019 march 9 20
2020 march 9 30
2021 march 9 20
2022 march 9 30 25

output 2022 march 9 30 25

Answer 1

It would be nice and very helpful, if you provide the following details to reproduce your scenario and give better answers.

(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) Desired output based on the sample data in the #1 above.
(3) Your SQL Server version (SELECT @@version;)

Thank you

Answer 2

Hi @Balram Pawar
Please check this query:

;WITH CTE AS  
(  
 SELECT *,CAST(CAST([Year]AS VARCHAR)+[Month]+CAST([Date]AS VARCHAR) AS DATE)AS Year_Month_Date  
 FROM #TEST  
)  
SELECT TOP(1)Year,Month,Date,Value,AVG(Value)OVER(ORDER BY Year)AS [Avg]  
FROM CTE  
WHERE DATEDIFF(YEAR,Year_Month_Date,GETDATE())<=5  
ORDER BY Year_Month_Date DESC  

If it is not working or helpful, please share us your table structure (CREATE TABLE …) and some sample data(INSERT INTO …),so that we can provide a tested, verifiable solution to your question.
Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Try this:

DECLARE @Table TABLE (  
	[Year] int,  
	[Month] varchar(20),  
	[Date] int,  
	[Value] int  
);  
  
INSERT INTO @Table VALUES  
(2017, 'March', 8, 15), (2018, 'March', 8, 25),  
(2019, 'March', 8, 15), (2020, 'March', 8, 25),  
(2021, 'March', 8, 15),  
(2017, 'March', 9, 20), (2018, 'March', 9, 30),   
(2019, 'March', 9, 20), (2020, 'March', 9, 30),  
(2021, 'March', 9, 20), (2022, 'March', 9, 30);  
  
;WITH CTE AS (  
	SELECT t2.[Year] AS StartYear,  
		   t1.[Year] AS EndYear,  
		   t1.[Month],  
		   t1.[Date]  
	FROM @Table AS t1  
	INNER JOIN @Table AS t2 ON t1.[Year] = t2.[Year] + 4 AND t1.[Month] = t2.[Month] AND t1.[Date] = t2.[Date]  
)  
  
SELECT c.[EndYear], t.[Date], AVG(t.[Value]) AS AvgValue  
FROM @Table AS t  
LEFT JOIN CTE AS c ON t.[Month] = c.[Month] AND t.[Date] = c.[Date]  
WHERE EXISTS (  
	SELECT 1 FROM CTE WHERE StartYear <= t.[Year] AND EndYear >= t.[Year] AND [Month] = t.[Month] AND [Date] = t.[Date]  
)  
GROUP BY c.[EndYear], t.[Month], t.[Date];  
GO