小型办公室账户管理的账户计算 [英] Accounts calculation for small office accounts management

问题描述

嗨朋友

为了管理现金交易，我们有以下格式的表格

< td> 5000

存款	退出	TransactionDate
1000	0	21 / 02/2013
0	300	22/02/2013
0	23/02/2013

如果我们在最后一次计算得到正确的余额



TotalDeposit - TotalWithdraw =余额



6000-300 = 5700



但是我们需要在网格视图中显示，也可以在Crystal报表中显示如下

存款	提取	余额	TransactionDate
1000	0	1000	21/02/2013
0	300	700	22/02/2013
5000	0	5700	23/02/2013

Deposi t |退出|平衡| TransactionDate

1000 0 1000 21/02/2013

0 300 700 22/02/2013

5000 0 5700 23/02/2013 < br $> b $ b

请帮我查询sql server中的查询方法或任何有关此问题的建议。

Hi Friends
To manage cash transactions we have table in below format

Deposit	Withdraw	TransactionDate
1000	0	21/02/2013
0	300	22/02/2013
5000	0	23/02/2013

If we do calculation at the last we get correct Balance

TotalDeposit - TotalWithdraw = Balance

6000-300=5700

But we require to display in grid view and also in Crystal report as below

Deposit	Withdraw	Balance	TransactionDate
1000	0	1000	21/02/2013
0	300	700	22/02/2013
5000	0	5700	23/02/2013

Deposit | Withdraw | Balance | TransactionDate
1000 0 1000 21/02/2013
0 300 700 22/02/2013
5000 0 5700 23/02/2013

Kindly help me how to query in sql server or any suggestion regarding this.

推荐答案

< br $>


检查以下示例....

Hi,

Check the following Sample....

-- Table Creation Statement
IF OBJECT_ID('TempDB..#TestSample') IS NOT NULL DROP TABLE #TestSample
CREATE TABLE #TestSample(Sno INT,Opening INT,Deposit INT,Withdraw INT,Closing INT,TransactionDate DATETIME)

-- Insert Sample Values
INSERT INTO #TestSample(Sno, Opening, Deposit, Withdraw, Closing, TransactionDate)
VALUES(1, 0, 1000, 0, 0, '2013/02/21'),(2, 0, 0, 300, 0, '2013/02/22'),(3, 0, 5000, 0, 0, '2013/02/23')

-- Required Output
SELECT T.Sno, (ISNULL((SELECT SUM((Opening+Deposit-Withdraw)) 
	    FROM #TestSample 
	    WHERE Sno < T.Sno),0)) 'OpeningBalance', 
T.Deposit, T.Withdraw, 
(ISNULL((SELECT SUM((Opening+Deposit-Withdraw)) 
	    FROM #TestSample 
	    WHERE Sno < T.Sno),0) + T.Deposit - T.Withdraw) 'ClosingBalance',
T.TransactionDate
FROM #TestSample T

这篇关于小型办公室账户管理的账户计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！