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 .

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.