Click here to Skip to main content
14,091,011 members
Rate this:
 
Please Sign up or sign in to 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???)
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
Updated 7-Mar-19 17:29pm
v2
Comments
Member 14173753 6-Mar-19 23:33pm
   
anyone can help me?
Rate this: bad
 
good
Please Sign up or sign in to vote.

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 ..
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
   
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!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

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

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[^]
   
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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) ";
   
v7
Comments
CHill60 8-Mar-19 4:05am
   
This is awful. Those multiple, and unnecessary, sub-queries is why it is so slow. I do not have the time necessary to rewrite this especially as you have completely disregarded the advice I gave you earlier. All I can suggest is that you go back to my solution and start again.
Member 14173753 8-Mar-19 5:24am
   
I use below quey, it final return 91 record (it's ok), but in form if user choose some condition as the date from x to y, and name is Jack... I can't apply fillter to MAIN WHERE, as:

query += " AND (R.TheDate between @fromdate and @todate) AND (P.TheDate between @fromdate and @todate) ";

query += " AND C.Name LIKE '%' + @TheName + '%'";

if user choose date then ERROR: Invalid colum name thedate


THIS IS MY QUERY:

Query= "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
on c.id_customer=r.id_customer
left join
(select sum(money) pmoney, id_customer from payment group by id_customer) as p
on c.id_customer=p.id_customer
where r.rmoney is not null or p.pmoney is not null" //here I Add to where clause
CHill60 13-Mar-19 5:15am
   
You have replied to your own comment which is why I did not see this until now.
You have to declare the variables @fromDate and @toDate within the sql query that will be executed. This is a dynamic query. It's not clear which language you are using to generate this, but you either need to use a parameterised query, or put your query into a stored procedure and pass the dates as parameters to that.

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web04 | 2.8.190518.1 | Last Updated 8 Mar 2019
Copyright © CodeProject, 1999-2019
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100