Progressive Sums in T-SQL

,

A common problem in sql is how to calculate and process, for the current row, information that is based on the results (sum, product, difference, etc) of the rows read so far.
A solution (we use) to this problem is to create a self join query based on the primary key using the condition of "greater or equal".
Suppose we have the following table:
[Table1] :
[ID] [numeric](18, 0) NOT NULL ,
[TheDate] [smalldatetime] NULL ,
[Credit] [float] NULL ,
[Debit] [float] NULL

We 'd like to produce an output that for every record displays the ID, CreditValue, DebitValue and Result:
Where Result = (Credit-Debit)[of the current row] +
Sum(Credit-Debit)[of all rows currently read for this ID].

--Create the table
CREATE TABLE [dbo].[Table1] (
	[ID] [numeric](18, 0) NOT NULL ,
	[TheDate] [smalldatetime] NULL ,
	[Credit] [float] NULL ,
	[Debit] [float] NULL 
) ON [PRIMARY]
GO


-- The sql statement produces an output for every diff ID:
SELECT     t1.ID, t1.Credit, t1.Debit, SUM(t2.Credit - t2.Debit) AS Result
FROM         Table1 t1 INNER JOIN
                      Table1 t2 ON t1.ID >= t2.ID
GROUP BY t1.ID, t1.Credit, t1.Debit
ORDER BY t1.ID

--A more complex situation arises, when we want the same
--result set (as above) but for every day('theDate')column.
--In In this case we have to use two segments in 
--the join: TheDate & ID.
SELECT     t1.TheDate, t1.ID, t1.Credit, t1.Debit, SUM(t2.Credit - t2.Debit) AS Result
FROM         Table1 t1 CROSS JOIN
                      Table1 t2
WHERE     (t1.TheDate > t2.TheDate) OR
                      (t1.TheDate = t2.TheDate) AND (t1.ID >= t2.ID)
GROUP BY t1.TheDate, t1.ID, t1.Credit, t1.Debit
ORDER BY t1.TheDate, t1.ID

Rate

2 (1)

Share

Share

Rate

2 (1)