Practice Activity - Creating a rolling total over the last 3 months in SQL Server
HTML-код
- Опубликовано: 6 сен 2024
- 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);
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!
Filip...Your films are "salt of the earth"
really happy to have such great channel
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
you are the life saver!
🙏 Thanks Sir
Would your logic work across multiple years? if you have 2023,2022 and 2021 for example?
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
i want to calculate rolling sum " every three month in last year"
plz teach me
thx
while loop?
That's a great idea. I've got a video coming out in a couple of weeks on this subject. Phillip
you couldn't make the work done with 'over partition by and row_number()) much easier?!
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