Practice Activity - Creating a rolling total over the last 3 months in SQL Server

If you are in March, how can you total January, February and March's figures in SQL Server?
My SQL Server Udemy courses are:
70-461, 70-761 Querying Microsoft SQL Server with T-SQL: rebrand.ly/que...
98-364: Database Fundamentals (Microsoft SQL Server): rebrand.ly/dat...
70-462 SQL Server Database Administration (DBA): rebrand.ly/sql...
Microsoft SQL Server Reporting Services (SSRS): rebrand.ly/sql...
SQL Server Integration Services (SSIS): rebrand.ly/sql...
SQL Server Analysis Services (SSAS): rebrand.ly/sql...
Microsoft Power Pivot (Excel) and SSAS (Tabular DAX model): rebrand.ly/mic...
----
You can create a running total using partition functions. However, what if you only wanted the running total to be over the last 3 months?
There are many ways of doing this. In this video, we will look at using a correlated query, and using a self-join.
We will also be looking at the DATEDIFF and DATEFROMPARTS functions.
Here is the code that we will be using to create the tables, if you want to use this as a Practice Activity:
DROP TABLE IF EXISTS Invoices;
CREATE TABLE Invoices
(InvoiceDate date,
InvoiceAmount int);
INSERT INTO Invoices
VALUES
('2022-12-01', 1), ('2023-02-01', 1), ('2023-02-15', 1),
('2023-03-01', 3), ('2023-04-01', 2), ('2023-04-15', 2),
('2023-05-01', 5), ('2023-06-01', 3), ('2023-06-15', 3), ('2023-08-01', 8);

[@BigRedFishDad]

MAN!!!!! I have been looking all over for this Thank you! EXACTLY what I needed... I found several "running" totals and other similar but this is EXACTLY what I needed for each month end close. I already adapted it to my tables and it is working like a charm, Great Video!

[@shadowitself]

Filip...Your films are "salt of the earth"
really happy to have such great channel

[@metalrouge2648]

My Solution in MS SQL using Cross apply By Rudolf.
with base_cte as (
select * ,DATEFROMPARTS(year(invoicedate),month(invoicedate),1) BoM from Invoices ),
sumit as (
select bom,sum (invoiceamount) InvoicesAmountTotal from base_Cte
group by bom)
select a.*, c.Running3MonthTotal from sumit a
cross apply (select sum(b.InvoicesAmountTotal ) Running3MonthTotal from sumit b
where b.BoM between DATEADD(month,-2,a.bom) and a.bom ) c

[@gracechen6800]

you are the life saver!

[@jagatkrishna1543]

🙏 Thanks Sir

[@b2224567]

Would your logic work across multiple years? if you have 2023,2022 and 2021 for example?

[@SQLServer101]

Yes - as you can see in this video, the 3 month running total for February includes the quantity from December the previous year. Good question. Phillip

[@user-qj1ht1bv5x]

i want to calculate rolling sum " every three month in last year"
plz teach me
thx
while loop?

[@SQLServer101]

That's a great idea. I've got a video coming out in a couple of weeks on this subject. Phillip

[@amirkhatibi4368]

you couldn't make the work done with 'over partition by and row_number()) much easier?!

[@SQLServer101]

Hi Amir. If you have an alternate solution, which works with missing months (so not all months have data), then why not post it? Phillip