Help me with caculate debit balace and incredit by SQL server 2012

Question

1.00/5 (1 vote)

See more:

I have tables:

Customer (Id_Customer, Name, Address)

       (1,A, Add1)
       (2,B, Add2)
       (3,C, Add3)
       (4,D, Add4)    
       (5,E, Add5)    

    Receipt (Id_Customer, Money)

       (2, 10)
       (3, 20)
       (2, 15)

    Payment (Id_Customer, Money)

       (1, 30)
       (2, 40)
       (4, 05)

Now, I want to show as below:

Name ------- Debit balance ------ In credit
    A ----------------------------------------30

    B ----------------15-------------------------

    C -----------------------------------------20

    D -------------- 05-----------------------

    Total DB: ----- 20-------------Total IC: 50

In that:

if sum(money) of Receipt < sum(money) of Payment
then Debit balance = sum(money) of Payment - sum(money) of Receipt
else
In credit = sum(money) of Receipt-sum(money) of Payment

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

What I have tried:

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???)

SQL

select a.*, Total_DebitBalance=sum(DebitBalance) over (), Total_InCredit=sum(InCredit) over () from (SELECT C.name, C.Address,

CASE WHEN SUM(isnull(R.Money, 0))< SUM(isnull(P.Money, 0)) THEN SUM(isnull(P.Money, 0)) - SUM(isnull(R.Money, 0)) END AS DebitBalance,

CASE WHEN SUM(isnull(R.Money, 0))> SUM(isnull(P.Money, 0)) THEN SUM(isnull(R.Money, 0)) - SUM(isnull(P.Money, 0)) END AS InCredit,

C.Id_Customer, COUNT(*) OVER () AS total_count

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, C.name, C.Address)a ;

THIS IS RESULT:

Name ------- Debit balance ------ In credit

B ----------------15-------------------------

Total DB: -----   15-------------Total IC: 0

Please help me. Thanks a lot.

Posted 6-Mar-19 16:00pm

Member 14173753

Updated 7-Mar-19 17:29pm

CHill60

v2

Add a Solution

Comments

Member 14173753 6-Mar-19 23:33pm

anyone can help me?

3 solutions

Solution 2

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 ..

SQL

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

SQL

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.

SQL

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.

SQL

;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

Posted 6-Mar-19 23:13pm

CHill60

Comments

Member 14173753 7-Mar-19 5:19am

Thanks CHiLl60 vere very much, I will check and please notice to you.

Thanks you.

Member 14174664 7-Mar-19 12:45pm

Dear CHill60,
I try and run Your Query but Result return not satisfy, it's only show customers that simultaneously in Receipt and Payment tables. It mean, the customer only in Receipt OR only in Payment will not return in result. Exactly, in my customer table have 604 customers, in that, 18 customer simultaneously in Receipt and Payment tables, 91 customers simultaneously in Receipt and Payment and Or in Receipt OR in Payment.
The query above only show 18 Customers. My hope must be 91 customers.

I think the problem are LEFT JOIN command? what do you think?
I know you are very busy, please spend a little time help me.

CHill60 7-Mar-19 15:38pm

Well if you look at the results I posted, clients who were only in receipts or clients who were only in payments did appear in the final results. Left join is appropriate otherwise they will be "left out" of the results.
Post your exact code and I will try to have another look tomorrow

Member 14173753 8-Mar-19 2:44am

CHIII60, I posted my query below, but it running very slow. if you have time, please me!

Solution 1

You cloud do something like this if it is not very large table.

SQL

select X.id,X.Name,X.ReceiptTotal, Y.PaymentTotal, (Y.PaymentTotal-X.ReceiptTotal) as InCreditBalance from (

(select 
c.id,
c.name,
sum(isnull(r.amount,0)) as ReceiptTotal
from Customer c
Left Join Receipt r on c.id=r.cust_id
group by c.id,c.name) X

  LEFT JOIN
  (
select 
c.id,
c.name,
sum(isnull(p.amount,0)) as PaymentTotal
from Customer c
left join Payment p on c.id=p.cust_id
group by c.id,c.name) Y
  on X.id=Y.id
  )

SQL Fiddle[^]

Posted 6-Mar-19 17:43pm

virang_21

Comments

Member 14173753 7-Mar-19 2:41am

I apply with the Your query but result not TRUE, returt all customers in table customer (about 1.000 rows, in that a lot of customer havn't Receipt OR payment) and DebitBalance,Incredit value don't map with DebitBalance and INcredit colums.

Can you improve my Query that I Tried.

please help me, urgent! Thank you.

Member 14173753 7-Mar-19 3:43am

anybody can help me!

CHill60 7-Mar-19 3:50am

Please note that we do this in our spare time, so it is not urgent to us. You have cross posted this in another forum - it's best to not do that as it can annoy members who might otherwise be able to help. This is just a friendly warning - I'll go and have a look at your problem now to see if I can help

Member 14173753 7-Mar-19 4:02am

Thanks you CHil60 very Much, and I'm sorry when say urgent. I hope that you can help me. again thanks for. I waiting you.

CHill60 7-Mar-19 5:13am

I've posted a solution. If you are still stuck comment on the solution and I will try to help further

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14173753 · Accepted Answer · 2019-03-07T17:30:00

dear ChiII60,

after research your help and everything on google and use below Query, but it running very slow when load and click next page on form C#. which this query, final rerult is 91 records (that i hope).
Can you help me to improve my query for shorter and FASTER. Thank you very much.

In Query, I use

... FROM Customer C CROSS JOIN Payment P CROSS JOIN Receipt R WHERE (P.Id_Customer=C.Id_Customer OR R.Id_Customer=C.Id_Customer)

query = "SELECT C.Name, C.Address, (case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)<(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer) then (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)-(SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer) when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)  end) as Debitbalance, (case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )>(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) then (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )-(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) when (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)  end) as InCredit, C.Id_Customer Id_Customer, COUNT(*) OVER () AS total_count, SUM(case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)<(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer) then (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)-(SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer) when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer)  end) OVER() AS Total_Debitbalance, SUM(case when (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )>(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) then (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer )-(SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) when (SELECT SUM(isnull(money, 0)) FROM Payment where Id_Customer=C.Id_Customer ) is null then  (SELECT SUM(isnull(money, 0)) FROM Receipt where Id_Customer=C.Id_Customer)  end) OVER() AS Total_InCredit from Customer C CROSS JOIN Receipt R CROSS JOIN Payment P where (P.Id_Customer=C.Id_Customer OR R.Id_Customer=C.Id_Customer) ";

Help me with caculate debit balace and incredit by SQL server 2012

3 solutions

Solution 2

Solution 1

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us