question

imrankasuri avatar image
imrankasuri asked

How to calculate balance on the bases of previous record?

Hi All, i am currently facing an issue and hope that someone will help me. here is the scenario. I have a simple table. Member Table ================ Member_id Member Name Opening Balance Opening Balance Type 1 David 5000 Debit Transaction Table ================= Transaction_IdTransaction DateMember_idDescriptionDebitCredit110/01/20111Deposit5000210/02/20111WidDrawl01000310/03/20111Deposit5000410/04/20111WidDrawl08000 Now what i need to show the Balance after each entry and its Type. The final result will be look like this. Transaction Details View =================================== Transaction_IdTransaction DateMember_idDescriptionDebitCredit Balance Balance Type 110/01/20111Deposit50005500 Debit210/02/20111WidDrawl010004500 Debit 310/03/20111Deposit50005000 Debit 410/04/20111WidDrawl080003000 Credit How can i create this view? the final balance is prepared by using the opening balance from the members table and then each calculation is made by the balance of previous record to the next and balance type is changed accordingly. I hope that some expert will help.
recursioncommon-table-expressionrownum
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image
WilliamD answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, sir, thank you very much for the reply. its really great effort. there is a little issue while executing the second approach. there is an error appear near to the ORDER BY T.Transaction_Id and i m not able to solve it. i am using the sql server 2005. Thanks in Advance.
0 Likes 0 ·
As I mentioned in the answer, the final (more compact) solution is using syntax that will be available in the next version of SQL Server (codename Denali) that is not yet on the market. I added it as a secondary solution so that you (and anyone else who reads this later on) know what will be possible in a future version of SQL Server.
0 Likes 0 ·
@WilliamD I posted a different answer which works for multiple members without recursion. It looks like the quirky update is still the way to go until Denali is finally here :)
0 Likes 0 ·
Oleg avatar image
Oleg answered
@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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

excellent counter point to my answer. Wish i could give more than 1 thumb up.
0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
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/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you very much Sir, i really appreciate, it is now working for me with excellent speed. i am new to sql server programming and don't have much knowledge about the CLR in SQL, so the above solutions is fine for me.. can you please guide me towards the resources from where i can learn the working with CLR of SQL Server? Thank you very much once again.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.