Скачать или смотреть Creating a running total in SQL Server, but restart it part way through the table.

In this video, we will be looking at how to create a running total in SQL Server using the OVER clause.
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: https://rebrand.ly/querying-microsoft...
98-364: Database Fundamentals (Microsoft SQL Server): https://rebrand.ly/database-fundamentals
70-462 SQL Server Database Administration (DBA): https://rebrand.ly/sql-server-dba
Microsoft SQL Server Reporting Services (SSRS): https://rebrand.ly/sql-server-ssrs
SQL Server Integration Services (SSIS): https://rebrand.ly/sql-server-ssis
SQL Server Analysis Services (SSAS): https://rebrand.ly/sql-server-ssas-mdx
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): https://rebrand.ly/microsoft-powerpiv...
----
My Excel for PC courses are:
Beginner to Excel - https://rebrand.ly/microsoft-excel-sp...
Intermediate to Expert Advanced - https://rebrand.ly/microsoft-excel-ex...
Power Table, Get and Transform and Power Pivot - https://rebrand.ly/visualizing-data-e...
VBA macros for Excel - https://rebrand.ly/excel-vba-pc
----
In this video, we will be looking at how to create a running total.
There are three parts to the OVER clause:
PARTITION BY - when do you want the calculation to restart in the middle of the total (if at all)?
ORDER BY - what order should the rows be in for the calculation. This can be different from the order that the query is finally displayed in.
ROWS/RANGE BETWEEN - what rows do you want to be included in the calculation? Does it go back to the very beginning of the query/partition, and then to the very end? Or is it smaller than that?
You can then use this with aggregations such as SUM or COUNT to create a calculation. And because SQL Server is expecting a different answer per row, you don't need to use the GROUP BY clause.
The code is:
drop table if exists #Input;
create table #Input
(a int,
b int,
c numeric(5,2))
insert into #Input values
(1, 10, null),
(2, null, 0.3),
(3, null, 0.32),
(4, null, 0.4),
(5, null, 0.3),
(6, null, 0.35),
(7, null, 0.36),
(8, null, 0.38),
(9, 20, null),
(10, null, 0.38),
(11, null, 0.34),
(12, null, 0.37),
(13, null, 0.38),
(14, null, 0.37),
(15, 30, null),
(16, null, 0.32),
(17, null, 0.34),
(18, null, 0.34),
(19, null, 0.34);
with #Input2 as(
SELECT *, COUNT(b) over(order by a
range between unbounded preceding
and current row) as d
from #Input
)
SELECT a, sum(b) over(partition by d
order by a
range between unbounded preceding
and current row)
isnull(sum(c) over(partition by d
order by a
range between unbounded preceding
and current row) ,0)
as b, c
FROM #Input2

and

DROP TABLE IF EXISTS #myTable
CREATE TABLE #myTable
(ID int,
Action varchar(10),
ID2 int IDENTITY(1,1))
INSERT INTO #myTable VALUES
(123, 'Action 1'),
(123, 'Action 1'),
(123, 'Action 2'),
(123, 'Action 2'),
(123, 'Action 1'),
(123, 'Action 1'),
(456, 'Action 1'),
(456, 'Action 2'),
(456, 'Action 2'),
(456, 'Action 3'),
(456, 'Action 3'),
(456, 'Action 1'),
(789, 'Action 1'),
(789, 'Action 2'),
(789, 'Action 3'),
(789, 'Action 2'),
(789, 'Action 2'),
(789, 'Action 2'),
(789, 'Action 3');
WITH #myTable2 as
(SELECT *, IIF(Action = LAG(Action,1)
over(partition by id
order by ID2), 0, 1) as Diff
FROM #myTable)
SELECT *, sum(Diff) over(PARTITION BY ID
ORDER BY ID2) AS [Target Value]
FROM #myTable2