SQL Training Online

  • Training Courses
  • Beginner Book
  • Blog

January 31, 2013 by Joey Blue Leave a Comment

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.

Video: 3 Day Moving Average in SQL


An efficient way to calculate a moving average in SQL using a few tricks to set date anchors.

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

Related

Filed Under: SQL Training Tagged With: SQL Dateadd, SQL Datediff, SQL Datetime, SQL Group By, SQL Having, SQL Moving Average, SQL Server 2012, SQL Subquery

Leave a Question, Comment, or Reply. All are welcome!Cancel reply

Recent Posts

  • SQL Database Normalization – 1NF, 2NF, 3NF, and 4NF
  • SQL Joins Tutorial for Beginners – Inner Join, Left Join, Right Join, Full Outer Join – SQL Training Online
  • Zillow House Price Analysis from CSV Archive – #PowerBI 002
  • Learn Basic SQL – 1 Hour Training Course – SQL Training Online
  • Create Table Statement in SQL Server and Inserting Baseball Homerun Leader Dataset – SQL Training Online

Popular Posts

  • SQL Functions
  • SQL Jobs in Oracle and Microsoft SQL Server
  • Troubleshooting a SQL Join
Copyright © 2025 · SQLTrainingOnline.com · Consulting at EmbarkBlue.com