# Calculate monthly balance from Credit and Debit tables in SQL Server

on Jul 12, 2021 01:23 AM
776 Views

Hi! I have two tables with data.

 Namber Date Debit 1 11.11.2020 1500 2 21.112020 10000 3 15.12.2020 12500 4 05.01.2021 11000 5 25.01.2021 20000 6 02.02.2021 15500 7 11.02.2021 1500 8 05.03.2021 13000

 Namber Date Credit 1 05.01.2021 1000 2 25.01.2021 2000 3 02.02.2021 5500 4 11.02.2021 1500 5 05.03.2021 3000

I want show result by month debit – credit = remain

 No Month Debit Credit Remain 1 November 11500 11500 2 December 12500 12500 3 January 31000 1000 30000 4 February 17000 7000 10000 5 March 13000 3000 10000

on Jul 12, 2021 01:53 AM

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

 Month Debit Credit Remain November 11500 0 11500 December 12500 0 12500 January 31000 3000 28000 February 17000 7000 10000 March 13000 3000 10000