Display records from database like Bank statement in SQL Server

Last Reply on Aug 05, 2017 02:50 AM By zameer

Posted on Aug 03, 2017 02:03 AM

Hi

I have Incoming and outgoing table

the table datafields are:

1.Date

2.Description

3.Amount

4.Paymenttype

5.Remarks

 

I need a query like bank passbook order by date

select date,decription,incoming,outgoing,balance from incoming a,outgoing b 

 

how can i get this.can you help anyone

Posted on Aug 05, 2017 02:50 AM Modified on on Aug 05, 2017 04:53 AM

Hi Vadivxaya,

Please refer the below sample.

SQL

DECLARE @AccountBalance TABLE
( 
	TransactionType CHAR(1)
	,[Date] DATETIME
	,[Description] VARCHAR(100)
	,Amount DECIMAL(10,2)
	,Paymenttype VARCHAR(100)
	,Remarks VARCHAR(100)
)
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:02:49.323','Credited',12000,'Cash','Done')
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:04:50.113','Credited',1300.45,'Cheque','Done')
INSERT INTO @AccountBalance VALUES('D','07/03/2017 15:00:10.413','Debited',1500,'RTGS','Done')
INSERT INTO @AccountBalance VALUES('C','07/04/2017 18:02:20.223','Credited',25000,'NEFET','Done')
INSERT INTO @AccountBalance VALUES('D','07/04/2017 03:02:01.123','Debited',25000,'NEFET','Done')
SELECT *
		,(
		ISNULL((SELECT SUM(Amount) 
				FROM @AccountBalance a 
				WHERE a.[Date]<= AB.[Date] AND TransactionType = 'C'),0) - 
		ISNULL((SELECT SUM(Amount) 
				FROM @AccountBalance a 
				WHERE a.[Date]<= AB.[Date] AND TransactionType = 'D'),0) 
		) BALANCE 
FROM @AccountBalance AB
ORDER BY [Date]

Output

TransactionType Date Description Amount Paymenttype Remarks BALANCE
C 7/1/2017 14:02 Credited 12000 Cash Done 12000
C 7/1/2017 14:04 Credited 1300.45 Cheque Done 13300.45
D 7/3/2017 15:00 Debited 1500 RTGS Done 11800.45
D 7/4/2017 3:02 Debited 25000 NEFET Done -13199.6
C 7/4/2017 18:02 Credited 25000 NEFET Done 11800.45

 You can also try below query

SQL

DECLARE @AccountBalance TABLE
( 
	TransactionType CHAR(1)
	,[Date] DATETIME
	,[Description] VARCHAR(100)
	,Amount DECIMAL(10,2)
	,Paymenttype VARCHAR(100)
	,Remarks VARCHAR(100)
)
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:02:49.323','Credited',12000,'Cash','Done')
INSERT INTO @AccountBalance VALUES('C','07/01/2017 14:04:50.113','Credited',1300.45,'Cheque','Done')
INSERT INTO @AccountBalance VALUES('D','07/03/2017 15:00:10.413','Debited',1500,'RTGS','Done')
INSERT INTO @AccountBalance VALUES('C','07/04/2017 18:02:20.223','Credited',25000,'NEFET','Done')
INSERT INTO @AccountBalance VALUES('D','07/04/2017 03:02:01.123','Debited',25000,'NEFET','Done')

DECLARE @PassBook TABLE 
(
TransactionID INT IDENTITY(1,1)
    ,TransactionType CHAR(1)
	,[Date] DATETIME
	,[Description] VARCHAR(100)
	,Amount DECIMAL(10,2)
	,Paymenttype VARCHAR(100)
	,Remarks VARCHAR(100)
	,Balance DECIMAL(18,2) DEFAULT 0
)

INSERT INTO @PassBook(TransactionType,[Date],[Description],Amount,Paymenttype,Remarks)
SELECT TransactionType,[Date],[Description],Amount,Paymenttype,Remarks FROM @AccountBalance

DECLARE @balance DECIMAL(18,2)
DECLARE @minID INT
DECLARE @maxID INT

SELECT @minID = (SELECT MIN(TransactionID) FROM @PassBook)
SELECT @maxID = (SELECT MAX(TransactionID) FROM @PassBook)

WHILE @minID<=@maxID
BEGIN
SELECT  @balance=SUM(CASE WHEN p1.TransactionType='C' 
            THEN P1.Amount ELSE -P1.Amount END)
            FROM @PassBook p1 WHERE TransactionID<=@minID

            UPDATE @PassBook SET Balance=@balance WHERE TransactionID=@minID
            SET @minID=@minID+1
END

SELECT * FROM @PassBook

Output

TransactionID TransactionType Date Description Amount Paymenttype Remarks Balance
1 C 7/1/2017 14:02 Credited 12000 Cash Done 12000
2 C 7/1/2017 14:04 Credited 1300.45 Cheque Done 13300.45
3 D 7/3/2017 15:00 Debited 1500 RTGS Done 11800.45
4 C 7/4/2017 18:02 Credited 25000 NEFET Done 36800.45
5 D 7/4/2017 3:02 Debited 25000 NEFET Done 11800.45