Calculate Opening and Closing Balance

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Calculate Opening and Closing Balance

Author

Topic

raringsunny
Starting Member

1 Post

Posted - 2012-03-11 : 04:00:19

Hi,

I need to write code in SQL to display following data in tabular form.

================================================

Date | Opening Bal | Charges (daily) | Deductions | Closing Balance

================================================

Opening Bal and Closing Bal are calculated columns.

What approach I should follow to calculate Opening and Closing Balance? The data needs to be processed for last one year.

What would be a best approach in SQL to generate such data?

I have read a few posts online which suggest usage of CTE's. But is there a more simple approach?

Will using a stored procedure and looping through all the records to calculate total on daily basis for last year be a better approach?

Any kind of help is appreciated.

Thanks!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-11 : 09:19:44

Looping through records is not the best approach. Some SQL experts derisively refer to that approach as RBAR (Row By Agonizing Row). Instead, you should try to use a set-based approach. Even with a set-based approach, SQL is not very good computing running totals (which is what you need to do).

There are couple of different approaches that you could use to compute the running total. In my opinion, the one using recursive CTE's is conceptually the simplest - but since you are not crazy about it, I won't mention that. Instead, here is an approach using a subquery.

declare @Jan1Balance int;
set @Jan1Balance = 200; -- assume $200 balance on the first day of the year.

SELECT
	a.Date,
	b.OpeningBalance + @Jan1Balance as OpeningBalance,
	b.OpeningBalance + @Jan1Balance + Income - Expense AS ClosingBalance
FROM
	YourTable a
	OUTER APPLY
	(
		SELECT SUM(b.Income-b.Expense) as OpeningBalance
		FROM YourTable b
		WHERE b.Date < a.DATE
	) b
WHERE
	a.Date >= '20120101';

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources