Note, only show Customer if Debit balance OR In credit is different from zero. And Totals over () for paging.

I run with QUERY, but ONLY show the Customers that also have 'money' in two tables Receipt and Payment (if customer only have money in Receipt OR Payment will don't show in result, WHY IS THAT? It CAN'T SHOW THAT cutomer???)

It seems that you want to get debit balance and incredit of every one(except E) and sum them up, if so you could try the code below.

with cte as(
select c.name ,
case when
isnull(p.pmoney,0) - ISNULL(r.rmoney,0)>0
then isnull(p.pmoney,0) - ISNULL(r.rmoney,0)
else null end
debitBanance,
case when
ISNULL(r.rmoney,0)-isnull(p.pmoney,0) >0
then ISNULL(r.rmoney,0)-isnull(p.pmoney,0)
else null end incredit
from
customer c left join
(select sum(money) rmoney,id_customer from receipt group by id_customer ) as r -- first sum up the table receipt to prevent duplicate record
on c.id_customer=r.id_customer
left join
(select sum(money) pmoney, id_customer from payment group by id_customer) as p -- first sum up the table payment to prevent duplicate record
on c.id_customer=p.id_customer
where r.rmoney is not null or p.pmoney is not null)
select * from cte
union
select 'total Db:',sum(debitBanance),sum(incredit) from cte

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Re: Please Help me with caculate Debit balace and InCredit by SQL server

If you want to be simple, I think you should remove the final row in the sql and you could sum in your client.

select c.name ,
case when
isnull(p.pmoney,0) - ISNULL(r.rmoney,0)>0
then isnull(p.pmoney,0) - ISNULL(r.rmoney,0)
else null end
debitBanance,
case when
ISNULL(r.rmoney,0)-isnull(p.pmoney,0) >0
then ISNULL(r.rmoney,0)-isnull(p.pmoney,0)
else null end incredit
from
customer c left join
(select sum(money) rmoney,id_customer from receipt group by id_customer ) as r -- first sum up the table receipt to prevent duplicate record
on c.id_customer=r.id_customer
left join
(select sum(money) pmoney, id_customer from payment group by id_customer) as p -- first sum up the table payment to prevent duplicate record
on c.id_customer=p.id_customer
where r.rmoney is not null or p.pmoney is not null and -- here write your where clause
order by c.name
offset 2 rows -- here write your pagination
fetch next 2 rows only

Best regards,

Ackerly Xu

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

Re: Please Help me with caculate Debit balace and InCredit by SQL server

If you want to filter data from original table, you could write where clause original select statement.

select c.name ,
case when
isnull(p.pmoney,0) - ISNULL(r.rmoney,0)>0
then isnull(p.pmoney,0) - ISNULL(r.rmoney,0)
else null end
debitBanance,
case when
ISNULL(r.rmoney,0)-isnull(p.pmoney,0) >0
then ISNULL(r.rmoney,0)-isnull(p.pmoney,0)
else null end incredit
from
customer where... here to filter customer table c left join
(select sum(money) rmoney,id_customer from receipt where ... here to filter receipt group by id_customer ) as r -- first sum up the table receipt to prevent duplicate record
on c.id_customer=r.id_customer
left join
(select sum(money) pmoney, id_customer from payment where .... here to filter payment group by id_customer) as p -- first sum up the table payment to prevent duplicate record
on c.id_customer=p.id_customer
where (r.rmoney is not null or p.pmoney is not null )
order by c.name
offset 1 rows
fetch next 2 rows only

Best regards,

Ackerly Xu

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

You could see B appears two times,because the date also appears , if you want to only show b once , you could use distinct.

select distinct debitBanance,name,incredit from (
select c.name ,
case when
isnull(p.pmoney,0) - ISNULL(r.rmoney,0)>0
then isnull(p.pmoney,0) - ISNULL(r.rmoney,0)
else null end
debitBanance,
case when
ISNULL(r.rmoney,0)-isnull(p.pmoney,0) >0
then ISNULL(r.rmoney,0)-isnull(p.pmoney,0)
else null end incredit,r.thedate rdate,p.thedate pdate
from
customer c left join
(select sum(money) over ( partition by id_customer) rmoney,* from receipt ) as r
on c.id_customer=r.id_customer
left join
(select sum(money) over ( partition by id_customer)pmoney ,* from payment) as p
on c.id_customer=p.id_customer
where (r.rmoney is not null or p.pmoney is not null) )
tt
order by name
offset 0 rows -- here write your pagination
fetch next 2 rows only

Best regards,

Ackerly Xu

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.