SQL Training Online

by 1 Comment

Moving Average with Windowing in SQL Server

The Moving Average used to be complex, but with the Windowing Functions, the Moving Average is easier and performs much better.

In this video I show you how to create a moving average in SQL using the OVER Clause and the ROWS Clause.

Video: SQL Moving Average with Windowing


How to use the SQL Over Clause for a Moving Average.

I use the Wide World Importers and the following scripts:

select [Invoice Date Key], sum(Profit) Profit
into dbo.ProfitByDate
from [Fact].[Sale]
group by [Invoice Date Key];

select 
[Invoice Date Key],
avg(Profit) OVER (ORDER BY [Invoice Date Key] ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) as AverageProfit
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Rolling Average with Windowing video.

by Leave a Comment

Format Function in SQL Server

The Format Function can help you change how a date or decimal is displayed.

In this video I do a quick demonstration of the Format Function.

Video: SQL Server Format Function


How to use the SQL Format Function on a Date.

I use the Wide World Importers and the following scripts:

select OrderDate,Format(OrderDate,'MMM dd, yyyy') 
from [Sales].[Orders]
where year(OrderDate) = 2016

Link to the Microsoft SQL Server Custom Date Formatters.

 

See the original  SQL Format for Date video.

by Leave a Comment

Union and Union All – How to use it in SQL Server

 

Union and Union All are very similar, but if you don’t understand the differences, it can cause trouble.

In this video I do a quick demonstration of the Union and Union All Function.

Video: SQL Server Union and Union All


How to use the SQL Server Union and Union All Operators to combine data sets.

I use the Wide World Importers and the following scripts:

select * from [Application].[People]
where PersonID between 1 and 5
union
select * from [Application].[People]
where PersonID between 4 and 9

 

See the original  SQL Union and Union All video.