SQL Server 2022

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

12 rows affected

WITH OldCTE AS (
    SELECT    customerId, baseCurrencyId, SUM(amount) AS totalOldDebt
    FROM      dbo.OldDebt
    WHERE     (isDeleted = 0)
    GROUP BY  customerId, baseCurrencyId
), RecoveredCTE AS (
    SELECT    customerId, baseCurrencyId, SUM(amount) AS totalRecoveredDebt
    FROM      dbo.RecoveredDebt
    WHERE     (isDeleted = 0)
    GROUP BY  customerId, baseCurrencyId
), MergedCTE AS (
    SELECT
        COALESCE(R.customerId, O.customerId) AS customerId,
        COALESCE(R.baseCurrencyId, O.baseCurrencyId) AS baseCurrencyId,
        ISNULL(O.totalOldDebt, 0) AS totalOldDebt,
        ISNULL(R.totalRecoveredDebt, 0) AS totalRecoveredDebt
    FROM OldCTE O
    FULL OUTER JOIN RecoveredCTE R
        ON R.customerId = O.customerId
        AND R.baseCurrencyId = O.baseCurrencyId
)
SELECT
    C.Id,
    M.baseCurrencyId,
    M.totalOldDebt,
    M.totalRecoveredDebt,
    M.totalOldDebt - M.totalRecoveredDebt AS totalDebt
FROM Customer AS C
JOIN MergedCTE M
    ON M.customerId = C.Id
ORDER BY
    C.id,
    M.baseCurrencyId

Id	baseCurrencyId	totalOldDebt	totalRecoveredDebt	totalDebt
1	2	100	100	0
2	1	2000	950	1050
2	2	300	500	-200
4	1	0	10	-10