Display records from database like Bank statement in SQL Server

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

 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