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