running sum based in dates

Shambhu Rai 1,406 Reputation points
2022-03-21T12:08:01.58+00:00

Hi Expert,

i wanted to calculate running sum based on dates and that would be 3rd column. how can i do this in sql server

create table main3( saleddate date, sales_count int)

insert [main3]
values('2022-03-03',1),
('2022-02-23',2499)

185178-image.png

Azure SQL Database
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,247 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
Accepted answer
  1. Guoxiong 8,126 Reputation points
    2022-03-21T14:36:29.057+00:00

    As @Olaf Helper mentioned, you can use the SUM() OVER ... statement to get your sales running total: 

    DECLARE @Table TABLE (  
	Saled_Date date,  
	Sales_Count int  
);  
INSERT INTO @Table VALUES ('2022-02-23', 2499), ('2022-03-03', 1);  
  
SELECT *, SUM(Sales_Count) OVER (ORDER BY Saled_Date) AS RunningTotal   
FROM @Table;

    185282-image.png

    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Olaf Helper 41,006 Reputation points
    2022-03-21T12:21:14.85+00:00

    In T-SQL your can use the OVER clause with SUM as aggregation, see https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15 => Example B. Using the OVER clause with aggregate functions