SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE ViewCustomerDebt (
    customerId INT,
    baseCurrencyId INT,
    totalOldDebt FLOAT,
    totalRecoveredDebt FLOAT,
    totalPaidDebt FLOAT
)
INSERT ViewCustomerDebt
VALUES
    (2, 1, 2000, 950, 0),
    (4, 1, 0, 10, 0),
    (1, 2, 100, 100, 0),
    (2, 2, 300, 500, 1000)

CREATE TABLE ViewCustomerSell (
    customerId INT,
    paymentTypeId INT,
    baseCurrencyId INT,
    subTotalPrice FLOAT,
    totalPrice FLOAT,
    totalCost FLOAT
)
INSERT ViewCustomerSell
VALUES
    (1, 1, 1, 5300, 5300, 5008),
    (2, 1, 1, 292.5, 287.5, 181),
    (2, 2, 1, 20638.5, 20538.5, 492),
    (2, 2, 2, 10, 10, 1000),
    (3, 1, 1, 174.5, 174.5, 160.5),
    (3, 2, 1, 1172, 1172, 6016),
    (4, 1, 1, 661, 661, 626),
    (4, 2, 1, 144, 144, 132.25)


CREATE TABLE ViewCustomerPurchase (
    customerId INT,
    paymentTypeId INT,
    baseCurrencyId INT,
    totalPrice FLOAT,
    totalCost FLOAT
)
INSERT ViewCustomerPurchase
VALUES
    (2, 2, 1, 91610, 0),
    (3, 2, 1, 115.5, 0)


  
CREATE TABLE Customer(
    id INT,
    name VARCHAR(100)
)
INSERT Customer
VALUES
    (1, 'Customer 1'),
    (2, 'Customer 2'),
    (3, 'Customer 3'),
    (4, 'Customer 4'),
    (5, 'Customer 5')
19 rows affected

select * from ViewCustomerDebt;
select * from ViewCustomerSell;
select * from ViewCustomerPurchase;


customerId
baseCurrencyId
totalOldDebt
totalRecoveredDebt
totalPaidDebt


2
1
2000
950
0

4
1
0
10
0

1
2
100
100
0

2
2
300
500
1000




customerId
paymentTypeId
baseCurrencyId
subTotalPrice
totalPrice
totalCost


1
1
1
5300
5300
5008

2
1
1
292.5
287.5
181

2
2
1
20638.5
20538.5
492

2
2
2
10
10
1000

3
1
1
174.5
174.5
160.5

3
2
1
1172
1172
6016

4
1
1
661
661
626

4
2
1
144
144
132.25




customerId
paymentTypeId
baseCurrencyId
totalPrice
totalCost


2
2
1
91610
0

3
2
1
115.5
0


WITH
	debt AS
	(
		SELECT 
			customerid,
			basecurrencyid,
			SUM(totalolddebt) AS old,
			SUM(totalrecovereddebt) AS recovered,
			SUM(totalpaiddebt) AS paid
		FROM viewcustomerdebt
		GROUP BY customerid, basecurrencyid
	),
	sell AS
	(
		SELECT
			customerid,
			basecurrencyid,
			SUM(CASE WHEN paymenttypeid = 1 THEN totalprice END) AS price_type1,
			SUM(CASE WHEN paymenttypeid = 2 THEN totalprice END) AS price_type2
		FROM viewcustomersell
		GROUP BY customerid, basecurrencyid
	),
	purchase AS
	(
		SELECT
			customerid,
			basecurrencyid,
			SUM(CASE WHEN paymenttypeid = 1 THEN totalprice END) AS price_type1,
			SUM(CASE WHEN paymenttypeid = 2 THEN totalprice END) AS price_type2
		FROM viewcustomerpurchase
		GROUP BY customerid, basecurrencyid
	)
SELECT
	COALESCE(debt.customerid, sell.customerid, purchase.customerid) AS customerid,
	COALESCE(debt.basecurrencyid, sell.basecurrencyid, purchase.basecurrencyid) AS basecurrencyid,
	sell.price_type1 AS total_paid_sell_price,
	sell.price_type2 AS total_debt_sell_price,
	sell.price_type2 + debt.old - debt.recovered AS total_sell_debt,
	purchase.price_type1 AS total_paid_purchase_price,
	purchase.price_type2 AS total_debt_purchase_price,
	purchase.price_type2 - debt.paid AS total_purchase_debt,
	(sell.price_type2 + debt.old - debt.recovered) - (purchase.price_type2 - debt.paid) AS total_debt_exchange
FROM debt
FULL OUTER JOIN sell ON sell.customerid = debt.customerid
										AND sell.basecurrencyid = debt.basecurrencyid
FULL OUTER JOIN purchase ON purchase.customerid = COALESCE(debt.customerid, sell.customerid)
												AND purchase.basecurrencyid = COALESCE(debt.basecurrencyid, sell.basecurrencyid)
ORDER BY
	COALESCE(debt.customerid, sell.customerid, purchase.customerid),
	COALESCE(debt.basecurrencyid, sell.basecurrencyid, purchase.basecurrencyid);


customerid
basecurrencyid
total_paid_sell_price
total_debt_sell_price
total_sell_debt
total_paid_purchase_price
total_debt_purchase_price
total_purchase_debt
total_debt_exchange


1
1
5300
null
null
null
null
null
null

1
2
null
null
null
null
null
null
null

2
1
287.5
20538.5
21588.5
null
91610
91610
-70021.5

2
2
null
10
-190
null
null
null
null

3
1
174.5
1172
null
null
115.5
null
null

4
1
661
144
134
null
null
null
null


Warning: Null value is eliminated by an aggregate or other SET operation.

customerid	basecurrencyid	total_paid_sell_price	total_debt_sell_price	total_sell_debt	total_paid_purchase_price	total_debt_purchase_price	total_purchase_debt	total_debt_exchange
1	1	5300	null	null	null	null	null	null
1	2	null	null	null	null	null	null	null
2	1	287.5	20538.5	21588.5	null	91610	91610	-70021.5
2	2	null	10	-190	null	null	null	null
3	1	174.5	1172	null	null	115.5	null	null
4	1	661	144	134	null	null	null	null

customerId	paymentTypeId	baseCurrencyId	subTotalPrice	totalPrice	totalCost
1	1	1	5300	5300	5008
2	1	1	292.5	287.5	181
2	2	1	20638.5	20538.5	492
2	2	2	10	10	1000
3	1	1	174.5	174.5	160.5
3	2	1	1172	1172	6016
4	1	1	661	661	626
4	2	1	144	144	132.25