Calculate monthly balance from Credit and Debit tables in SQL Server

Hi PRA,

Refer below query.

SQL

DECLARE @Debit AS TABLE(Namber INT,Date DATE,Debit INT)
INSERT INTO @Debit VALUES(1,'11.11.2020',1500)
INSERT INTO @Debit VALUES(2,'11.21.2020',10000)
INSERT INTO @Debit VALUES(3,'12.15.2020',12500)
INSERT INTO @Debit VALUES(4,'01.05.2021',11000)
INSERT INTO @Debit VALUES(5,'01.25.2021',20000)
INSERT INTO @Debit VALUES(6,'02.02.2021',15500)
INSERT INTO @Debit VALUES(7,'02.11.2021',1500)
INSERT INTO @Debit VALUES(8,'03.05.2021',13000)

DECLARE @Credit AS TABLE(Namber INT,Date DATE,Credit INT)
INSERT INTO @Credit VALUES(1,'01.05.2021',1000)
INSERT INTO @Credit VALUES(2,'01.25.2021',2000)
INSERT INTO @Credit VALUES(3,'02.02.2021',5500)
INSERT INTO @Credit VALUES(4,'02.11.2021',1500)
INSERT INTO @Credit VALUES(5,'03.05.2021',3000)

SELECT t.[MonthName] 'Month',
	SUM(t.Debit) 'Debit',
	SUM(t.Credit) 'Credit',
	SUM(t.Debit) - SUM(t.Credit) 'Remain'
FROM 
(
	SELECT DATENAME(MONTH,Date) MonthName,
	Debit,
	0 'Credit',
	DATEPART(YYYY,Date) 'Year'
	FROM @Debit
	UNION 
	SELECT DATENAME(MONTH,Date),
	0 'Credit',
	Credit,
	DATEPART(YYYY,Date) 'Year'
	FROM @Credit
) t
GROUP BY t.[MonthName],t.[Year]
ORDER BY t.[Year] ASC,
	CASE t.[MonthName] 
		WHEN 'January' THEN 1
		WHEN 'February' THEN 2
		WHEN 'March' THEN 3
		WHEN 'April' THEN 4
		WHEN 'May' THEN 5
		WHEN 'June' THEN 6
		WHEN 'July' THEN 7
		WHEN 'August' THEN 8
		WHEN 'September' THEN 9
		WHEN 'October' THEN 10
		WHEN 'November' THEN 11
		WHEN 'December' THEN 12
	END

Output