Here we go
The reason that some customers are not appearing is because you are not handling NULL values. The SUM function will ignore nulls when actually doing the summation, but if
all the values are NULL it will also return NULL. That's probably easier to understand with a query ..
select C.Id_Customer, [Name], [Address], SUM(R.Money) AS receipt, SUM(P.Money) AS payment
FROM #Customer C
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer
GROUP BY C.Id_Customer, Name, Address
gives the results
1 A Add1 NULL 30
2 B Add2 25 80
3 C Add3 20 NULL
4 D Add4 NULL 5
5 E Add5 NULL NULL
The first step therefore is to change those NULLs into a value - i.e. zero
select C.Id_Customer, [Name], [Address],
ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0) AS payment
FROM #Customer C
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer
GROUP BY C.Id_Customer, Name, Addres
Now we have the basis on which we can build our conditions listed. We could do this as a sub-query e.g.
SELECT Id_Customer, [Name], [Address],
Debit_Balance = CASE WHEN receipt < payment THEN payment - receipt ELSE 0 END,
In_Credit = CASE WHEN receipt < payment THEN 0 ELSE receipt - payment END
FROM
(select C.Id_Customer, [Name], [Address], ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0) AS payment
FROM #Customer C
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer
GROUP BY C.Id_Customer, Name, Address) Q
Or we could use a "Common Table Expression" CTE (
Introduction to Common Table Expressions (CTE's) - Essential SQL[
^]) e.g.
;with Q as
(
select C.Id_Customer, [Name], [Address], ISNULL(SUM(R.Money),0) AS receipt, ISNULL(SUM(P.Money),0) AS payment
FROM #Customer C
LEFT JOIN #Receipt R ON C.Id_Customer = R.Id_Customer
LEFT JOIN #Payment P ON C.Id_Customer = P.Id_Customer
GROUP BY C.Id_Customer, Name, Address
)
SELECT Id_Customer, [Name], [Address],
Debit_Balance = CASE WHEN receipt < payment THEN payment - receipt ELSE 0 END,
In_Credit = CASE WHEN receipt < payment THEN 0 ELSE receipt - payment END
FROM Q
Both of these return exactly the same results..
1 A Add1 30 0
2 B Add2 55 0
3 C Add3 0 20
4 D Add4 5 0
5 E Add5 0 0