SQL to get Running Total of Transactions

The below query helps the user to get the running total of transaction based on date, consider below tables for example:

Step 1: Create a view with following query (Query 1):

USE [Demo]

GO

SET

ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE VIEW [dbo].[vwFactTransactionAgg]

AS

SELECT TOP (100) PERCENT CustomerKey, DateKey, SUM(Amount) AS Amount

FROM dbo.FactTransaction

GROUP BY CustomerKey, DateKey

ORDER BY CustomerKey, DateKey

GO


Step 2: Execute the below Query



SELECT

T.CustomerKey ,

T.DateKey ,

T.Amount ,

RT.RunningTotal

FROM vwFactTransactionAGG T

CROSS APPLY (SELECT SUM(Amount) AS runningTotal

FROM vwFactTransactionAgg

WHERE DateKey <= t.Datekey AND CustomerKey = t.CustomerKey

) AS RT

ORDER BY T.CustomerKey, DateKey