SQL Training Online

by 1 Comment

Year-to-Date using Partition By with Windowing – SQL Training Online

You can use the Partition By Function in SQL Server to get a Year-to-Date and Month-to_Date calculation.

In this video I show you how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

This video builds on the prior day’s video, Prior Day Profit using the Lag Function with Windowing .

Video: Month-to_Date using the Partition By Function in SQL Server


how to create a Year-to-Date value using the Windowing Partition By Function in TSQL.

I use the Wide World Importers and the following scripts:

--SQL Year-to-Date using Partition By
select 
year([Invoice Date Key]) as Year,
Month([Invoice Date Key]) as Month,
[Invoice Date Key],
Profit,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]), Month([Invoice Date Key]) ORDER BY [Invoice Date Key]) as MonthToDate,
sum(Profit) OVER (PARTITION BY year([Invoice Date Key]) ORDER BY [Invoice Date Key]) as YearToDate
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original Year-to-Date using the Partition Windowing Function video.

by 1 Comment

Prior Day Profit using the Lag Function with Windowing in SQL Server

You can get a prior day value using the Lag Function in SQL Server.

In this video I show you how to create a prior day squeeze of profit using the Windowing Lag function in TSQL.

This video builds on the prior day’s video, Moving Average with Windowing.

Video: Prior Day Profit using the Lag Function in SQL Server


How to use the SQL Lag Function for Prior Day Squeeze.

I use the Wide World Importers and the following scripts:

select 
[Invoice Date Key],
Profit,
Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]) as PriorDayProfit,
Profit - isnull(Lag(Profit,1) OVER (ORDER BY [Invoice Date Key]),0) as DailySqueeze
from dbo.ProfitByDate
order by 1

Link to the Microsoft SQL Server Over Clause.

 

See the original SQL Prior Day Profit – Lag Windowing Function video.

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.