SQL Training Online

by 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

by Leave a Comment

How to use the SQL In Statement with Subquery

Video: How to use the SQL In Statement with Subquery – SQL Training Online
In this video, I show you how to use the SQL IN Statement with Subquery in SQL Server 2012 using SQL Server Management Studio.
[sharebox4 sharetext=”Share This Page”] [/sharebox4]

How do you use the SQL IN Statement with a Subquery?

I will show you in 3 steps that build on each other:

  1. Use the SQL OR operator.
  2. Use the SQL IN operator.
  3. Substitute a SQL Subquery into the IN Clause.

I will start with a simple query of the SQL Training Online Simple DB employee table where I want to retrieve 3 employee numbers: 7369,7499,7521

select * from employee
where employee_number=7369
or employee_number=7499
or employee_number=7521

 

Here, you can see that I used the SQL OR statement to get the 3 employees.

But, I can make this a little cleaner by using the SQL IN statement:

select * from employee
where employee_number in (7369,7499,7521)

 

This query returns the same data as before, but it has less typing.

SQL In Statement With Subquery

So, now I want to define a subquery. It is simply:

“A subquery is a query inside of another query.”

Let’s start the example. First, I will look at the customer table and specifically, the salesman employee number:

select saleman_employee_number from customer

 

This query returns a list of employee numbers attached to a customer. But, I want to filter out the nulls and get rid of the repeated employee numbers. So, here is the query for that.

select distinct saleman_employee_number from customer
where saleman_employee_number is not null

 

Now, this is where the magic of a subquery comes in. Since this query returns a list of values, you can simply use it in the original query as the SQL IN statement. It looks like this:

select * from employee
where employee_number in (select distinct saleman_employee_number from customer where saleman_employee_number is not null)

 

And, that’s it. You have now used the SQL IN statement with Subquery.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: https://www.sqltrainingonline.com

Twitter: http://www.twitter.com/sql_by_joey

Google+: https://plus.google.com/#100925239624117719658/posts

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline