Calculate Trail balance with opening and closing balance in SQL Server

Hi ishuhasan21,

Please refer below query.

SQL

CREATE TABLE Accounts
(
	AccID VARCHAR(50),
	AccName VARCHAR(50),
	AccOpBal DECIMAL
)
INSERT INTO Accounts VALUES('123','Cash acct','1200')
INSERT INTO Accounts VALUES('456','Bankacct','1300')

 CREATE TABLE Transactions
 (
	TransID INT,
	AccNo VARCHAR(50),
	TransDt DATE,
	TransDebit DECIMAL,
	TransCredit DECIMAL
)
INSERT INTO Transactions VALUES('123123','Cash acct','4-4-2023',100,0)
INSERT INTO Transactions VALUES('12123','Cash acct','5-4-2023',200,300)
INSERT INTO Transactions VALUES('12323','Cash acct','6-4-2023',0,50)
INSERT INTO Transactions VALUES('12313','Bankacct','4-4-2023',0,300)
INSERT INTO Transactions VALUES('1213','Bankacct','5-4-2023',200,100)
INSERT INTO Transactions VALUES('1313','Bankacct','6-4-2023',500,0)

SELECT t.AccNo,t.TransDt, a.AccOpBal,t.TransDebit, t.TransCredit INTO #temp1 
FROM Transactions t join Accounts a ON a.AccName = t.AccNo

SELECT AccNo,
	   Transdt,
	   LAG(ClosingAcc,1,AccOpBal) OVER(PARTITION BY AccNo ORDER BY Transdt) AS AccOpBal,
	   TransDebit,
	   TransCredit,
	   ClosingAcc AS ClosingAcc
FROM 
(
	SELECT AccNo,
		   Transdt,
		   AccOpBal,
		   TransDebit,
		   TransCredit,
		   AccOpBal+ SUM(TransDebit-TransCredit) OVER (PARTITION BY AccNo ORDER BY Transdt) AS ClosingAcc
	FROM #temp1
)
AS subquery ORDER BY AccNo DESC

Output