How to Calculate a SQL Moving Average without a Cursor

Update:  If you are working with the newest versions of SQL Server, you can use the windowing functions to accomplish the same thing.  I posted the updated code at the end of the post.  For this video, I still like the thought process of anchoring to a date.

There are debates on the best way to do a SQL Moving Average in SQL Server.

Some people think there are times when a cursor is most efficient.  Other’s think that you can do it all in a set-based way without the cursor.

The other day I was going to calculate a moving average and my first thought was to use a cursor.

I did some quick research and found this forum question: Moving Average in TSQL

There is a post that shows a subquery with an anchor date to help find the 1 and 2 day offset.

Here is the script you can use to test the 3 day SQL Moving Average final result.

CREATE TABLE [dbo].[daily_sales](
    [id] [int] NULL,
    [dt] [datetime] NULL,
    [revenue] [decimal](12, 2) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (1, CAST(0x0000A15700000000 AS DateTime), CAST(125.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (2, CAST(0x0000A15800000000 AS DateTime), CAST(114.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (3, CAST(0x0000A15900000000 AS DateTime), CAST(92.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (4, CAST(0x0000A15A00000000 AS DateTime), CAST(152.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[daily_sales] ([id], [dt], [revenue]) 
VALUES (5, CAST(0x0000A15B00000000 AS DateTime), CAST(48.00 AS Decimal(12, 2)))
GO

Here is the final query.

SELECT        
            DATEADD(DAY, days_since_1900, '19000101') AS dt,
            AVG(revenue) AS Revenue_3_day_moving_average
FROM (
            select 
            DATEDIFF(DAY, '18991230', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL

            select 
            DATEDIFF(DAY, '18991231', dt) AS days_since_1900
            ,revenue
            ,0 as Actualdate
            from daily_sales

            UNION ALL
            select 
            DATEDIFF(DAY, '19000101', dt) AS days_since_1900
            ,revenue
            ,1 as Actualdate
            from daily_sales

) AS daily_sales_normalized_to_1900
GROUP BY    days_since_1900
HAVING        MAX(Actualdate) = 1
ORDER BY    dt

Here is the query you would use with SQL Server 2012.

select dt, 
    avg(revenue)
    OVER (
        ORDER BY dt
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )
FROM daily_sales

The original Youtube video: SQL Moving Average

Tags: SQL Dateadd, SQL Datediff, SQL Datetime, SQL Group By, SQL Having, SQL Moving Average, SQL Server 2012, SQL Subquery