Calculate monthly balance from Credit and Debit tables in SQL Server

PRA
 
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

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
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