question

You didn't mention which version of SQL Server you were using, but going by your tag of recursive cte I guess you are at least on SQL 2005. The following will achieve what you want: /* Test Setup */ DECLARE @MemberId int = 1 DECLARE @Member AS TABLE (Member_id int, MemberName varchar(255), OpeningBalance int, OpeningBalanceType varchar(20), PRIMARY KEY CLUSTERED (Member_id)); INSERT INTO @Member (Member_id, MemberName, OpeningBalance, OpeningBalanceType) SELECT 1, 'David', 5000, 'Debit'; DECLARE @Transactions AS TABLE (Transaction_Id int, TransactionDate datetime, Member_id int, Description varchar(20), Debit int, Credit int, PRIMARY KEY CLUSTERED (Transaction_Id)); INSERT INTO @Transactions (Transaction_Id, TransactionDate, Member_id, Description, Debit, Credit) SELECT 1,'10/01/2011',1,'Deposit',500,0 UNION ALL SELECT 2,'10/02/2011',1,'WidDrawl',0,1000 UNION ALL SELECT 3,'10/03/2011',1,'Deposit',500,0 UNION ALL SELECT 4,'10/04/2011',1,'WidDrawl',0,8000; /* End Setup */ /* Recursive CTE (works on SQL 2005 and above, and is expensive with large amounts of data! */ ; WITH TranHistory AS (SELECT Transaction_Id, TransactionDate, M.Member_id, Description, Debit, Credit, (OpeningBalance + Debit) - Credit Balance, OpeningBalanceType FROM @Member M INNER JOIN @Transactions T ON M.Member_id = T.Member_id WHERE T.Transaction_Id = 1 AND T.Member_id = @MemberId UNION ALL SELECT T.Transaction_Id, T.TransactionDate, H.Member_id, T.Description, T.Debit, T.Credit, (H.Balance + T.Debit) - T.Credit Balance, CAST(CASE WHEN T.Debit > 0 THEN 'Debit' ELSE 'Credit' END AS varchar(20)) FROM TranHistory H INNER JOIN @Transactions T ON H.Member_id = T.Member_id AND H.Transaction_Id + 1 = T.Transaction_Id) SELECT Transaction_Id, TransactionDate, Member_id, Description, Debit, Credit, Balance, OpeningBalanceType FROM TranHistory OPTION (MAXRECURSION 0 ); -- OPTION required so that recursion will continue without aborting when maximum recursion level is reached. The problem with recursion is, that it is expensive on resources. The more history you have for a member, the longer this will take to run. As of Denali (next version of SQL Server, there is a much more elegant way of getting what you want. It is also **much** faster and doesn't require recursion. SUM() can now take an ORDER BY to specify which order should be used for running the aggregation. This will do a simple table scan/index scan to get the data and sort the data for the aggregation. It is much faster and the code is easier to understand too: /* The new possibility of running totals offered by Denali onwards = SUM() OVER (ORDER BY) */ SELECT T.Transaction_Id, TransactionDate, T.Member_id, T.Description, T.Debit, T.Credit, M.OpeningBalance + SUM(T.Debit - T.Credit) OVER (PARTITION BY T.Member_id ORDER BY T.Transaction_Id), -- An order by in the SUM allows for a running total, and is blazing fast too! CAST(CASE WHEN T.Debit > 0 THEN 'Debit' ELSE 'Credit' END AS varchar(20)) BalanceType FROM @Member M INNER JOIN @Transactions T ON M.Member_id = T.Member_id WHERE M.Member_id = @MemberId

@WilliamD already provided you with a good start, but because there are some points in his answer which need attention, I would like to discuss them and also offer a different solution. One small flaw in the original answer is that it only works for a single Member ID. Also, it makes assumption that the transaction IDs are sequential which may not be true in case if there are transactions for multiple members. For example, try using the same mockup data as in the original answer but insert 2 records in the Member table (Id = 1 for David starting form 5000 debit and ID = 2 for Oleg starting from 3000 credit). Now, lets insert some transaction records such that while the Transaction_Ids are sequential, they belong to different members: insert into @Transactions select 1,'10/01/2011',1,'Deposit',500,0 union all select 2,'10/01/2011',2,'Deposit',500,0 union all select 3,'10/02/2011',1,'WidDrawl',0,1000 union all select 4,'10/02/2011',2,'WidDrawl',0,1000 union all select 5,'10/03/2011',1,'Deposit',500,0 union all select 6,'10/03/2011',2,'Deposit',500,0 union all select 7,'10/04/2011',1,'WidDrawl',0,8000 union all select 8,'10/04/2011',2,'WidDrawl',0,8000; From the question definition it looks like that every amount always has to be expressed as a positive integer which means that the logic for determining the balance type must not be based on the debit - credit but rather on whether the Balance is positive or not. From the data above we should expect that David should end up with minus 3000, expressed as 3000 in Balance and Credit in the Balance Type (initial 5000 plus 2 deposits of 500 each, a withdrawal of 1000 and withdrawal of 8000). Oleg should end up 11K in the hole. The query to do it uses a quirky update technique. This works only and only if the table is properly clustered and the query processing is reduced to utilize only one processor: -- lets create a table from the join of data in the member and transactions -- tables and add 3 columns to it: N - sequential row number, Balance and -- BalanceType. This table will be clustered by the sequential row number. declare @results table ( N int not null primary key clustered, Transaction_Id int not null, TransactionDate datetime not null, Member_Id int not null, [Description] varchar(20) not null, Debit int not null, Credit int not null, Balance int not null, BalanceType varchar(20) ); -- insert data into results table making the Balance to be equal to the -- OpeningBalance for now and BalanceType set to null insert into @results select row_number() over (order by m.Member_id, t.Transaction_Id) N, t.Transaction_Id, t.TransactionDate, m.Member_Id, t.[Description], t.Debit, t.Credit, Balance = m.OpeningBalance * case when OpeningBalanceType = 'Debit' then 1 else -1 end, BalanceType = null from @Member m inner join @Transactions t on m.Member_id = t.Member_Id; -- Now the quirky update part: declare @member_id int; declare @balance int; declare @anchor int; update @results set @balance = case when Member_Id = @member_id then @balance + Debit - Credit else Balance + Debit - Credit end, Balance = abs(@balance), -- always express Balance as a positive amount BalanceType = case when @balance >= 0 then 'Debit' else 'Credit' end, @member_id = Member_Id, @anchor = N option (maxdop 1); -- final select which shows the results: select * from @results; -- results (I omit the first few columns so the results fit into this window): Member_Id Description Debit Credit Balance BalanceType ----------- -------------------- ----------- ----------- ----------- ----------- 1 Deposit 500 0 5500 Debit 1 WidDrawl 0 1000 4500 Debit 1 Deposit 500 0 5000 Debit 1 WidDrawl 0 8000 3000 Credit 2 Deposit 500 0 2500 Credit 2 WidDrawl 0 1000 3500 Credit 2 Deposit 500 0 3000 Credit 2 WidDrawl 0 8000 11000 Credit Oleg

What you want to achieve is a typical sample of Running Totals. There are a few methods and @Oleg presented one of the fastest one. Generally mostly such calculation is not needed directly in T-SQL and if it is for reporting purposes it's better to move such calculations into the Reporting layer rather than do such calculations in T-SQL. Even if you need this eg. for generating flat files export, then it's better to move such things into the ETL. However if you want to achieve maximum possible speed and you are on SQL Server 2005, you can use a CLR. The CLR solution is unbeatable in this situation comparing to any other method you can find over internet. You can take a look on a sample CLR Solution: [SQL Server and fastest running totals using CLR][1] Of course to support balances for multiple `MemberID`s in single view, you will have to update the CLR method to pass and store the `MemberID` and `OpeningBalance` to the CLR Method, store the `MemberID` in the same way as the Running Total using the `CallContext` and upon the change of the ID reset the Running Total by the currently provided opening balance. So the CLR for your solution could look like this one: public class RunningTotal { private struct RTData { public RTData(SqlInt32 memberID, SqlMoney runningTotal) { MemberID = memberID; RunningTotal = runningTotal; } public SqlInt32 MemberID; public SqlMoney RunningTotal; } [SqlFunction(IsDeterministic = true)] public static SqlMoney RunningTotalsMoney(SqlInt32 memberID, SqlMoney openingBalance, SqlMoney val, SqlByte id, SqlMoney nullValue) { string dataName = string.Format("MulstiSqlRt_{0}_{1}", typeof(SqlMoney).FullName, id.IsNull ? 0 : id.Value); object lastSum = CallContext.GetData(dataName); SqlMoney currentValue = val.IsNull ? nullValue : val; SqlMoney total = SqlMoney.Null; SqlInt32 lastID = SqlInt32.Null; if (lastSum != null && ((RTData)lastSum).MemberID == memberID) total = ((RTData)lastSum).RunningTotal; else total = openingBalance; if (!val.IsNull) total = total.IsNull ? val : total + val; else total = total.IsNull ? nullValue : (nullValue.IsNull ? total : total + nullValue); CallContext.SetData(dataName, new RTData(memberID, total)); return total; } } [1]: http://www.pawlowski.cz/2010/09/sql-server-and-fastest-running-totals-using-clr/