Calculate Moving Avg using SQL

Transact-SQL
1

I need to calculate the moving average

I am able to get this using Alteryx but not able to get the desired result using SQL.

Please check Data below.

CREATE TABLE [dbo].[MovingAvg]
(
[Period] [date] NULL,
[Amount] [float] NULL
)

INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-01-01' AS Date), 1)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-02-01' AS Date), 2)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-03-01' AS Date), 3)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-04-01' AS Date), 4)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-05-01' AS Date), 5)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-06-01' AS Date), 6)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-07-01' AS Date), null)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-08-01' AS Date), null)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-09-01' AS Date), null)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-10-01' AS Date), null)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-11-01' AS Date), null)
GO
INSERT [dbo].[MovingAvg] ([Period], [Amount]) VALUES (CAST(N'2016-12-01' AS Date), null)
GO
Q

1 Like
2

Which specific version of SQL Server are you on? SQL 2012? 2008? 2016? The version effects the best answer here.

3

Hi Scott, I am using 2012.

4

try this you might find a better solution from somebody else but this work:-

CREATE TABLE #MovingAvg
 (
 [Period] [date] NULL,
 [Amount] [float] NULL
 )

INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-01-01' AS Date), 1)


 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-02-01' AS Date), 2)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-03-01' AS Date), 3)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-04-01' AS Date), 4)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-05-01' AS Date), 5)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-06-01' AS Date), 6)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-07-01' AS Date), null)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-08-01' AS Date), null)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-09-01' AS Date), null)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-10-01' AS Date), null)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-11-01' AS Date), null)

 INSERT #MovingAvg ([Period], [Amount]) VALUES (CAST(N'2016-12-01' AS Date), null)
 GO



declare @Begin int
declare @end int
set @Begin = 1 
set @end = 	(select CounT(*) from #MovingAvg where Amount is NULL)

while @Begin <= @end begin 

declare @avg float
declare @period date

set @period = (select top 1 period from #MovingAvg where Amount is NULL)
set @avg = (
				select Sum(amount)/3 
				from (
						select top 3 
						amount
						from #MovingAvg
						where amount is not null 
						order by Period desc
				)elma
			)

	update #MovingAvg
	set amount = @avg
	where period = @period

set @Begin = @Begin  + 1
	
end 
GO

 select * from #MovingAvg
 GO
1 Like
5

This is really helpful

6

Welcome @bhagii to SQL Team

1 Like
7

The first one is easy, since all the previous values are not NULL, but I haven't yet figured out how to get the LAG to use the newly computed values instead of the original NULL values.

SELECT
    CASE WHEN Amount IS NOT NULL THEN Amount
    ELSE (LAG(Amount, 1) OVER(ORDER BY Period) + LAG(Amount, 2) OVER(ORDER BY Period) +
          LAG(Amount, 3) OVER(ORDER BY Period)) / 3 END AS Amount
FROM dbo.MovingAvg
ORDER BY Period
8

I think you have to use a recursive CTE in this case (I'd love to be proved wrong), which would look something a bit like:

with cte_movingAverage as
(
select top (1)
	Period,
	Cast(Null As Float) As Avg1,
	Cast(Null As Float) As Avg2,
	Amount As Avg3 
from movingAvg
order by Period
union all

select
	MA.Period,
	C.Avg2 As Avg1,
	C.Avg3 As Avg2,
	Case 
		When MA.Amount Is Not Null Then MA.Amount
		Else (C.Avg1 + C.Avg2 + C.Avg3) / 3.0
	End As Avg3
from cte_movingAverage C
join MovingAvg MA On DateAdd(Month, 1, C.Period) = MA.Period
)
Select 
	Period,
	Avg3 As Average
from cte_movingAverage
9

I don't think you can be proved wrong. OP is trying to calculate a variation of exponential moving average. If s/he were trying to calculate simple moving average (which is just a trailing average), or exponential moving average (which gives more weight to recent data points, the weight distribution determined by a smoothing constant), windowing functions would work very well. But in this case, the only choices are recursive cte, a while loop, or a cursor. My anecdotal experience has been that the while loop performs better than recursive cte in these types of cases, especially as the number of rows increase.