Delete Lines from table if the sum of multiple lines is Nil (based on criteria)

Question

I'm working with the following table where you can get activity from customer purchases.

DateOfActivity	CustomerReference	Reference Line	Description	Receivable Amount
24/10/2022	CUST567	1	Credit Purchase	20,000
24/10/2022	CUST567	4	Credit Purchase	10,000
24/10/2022	CUST555	2	Credit Purchase	50,000
27/10/2022	CUST555	2	Contract Sign	0
27/10/2022	CUST567	4	Contract Sign	0
27/10/2022	CUST567	1	Contract Sign	0
27/10/2022	CUST567	4	Repayment	-3,500
27/10/2022	CUST567	4	Repayment	-6,500
13/11/2022	CUST567	1	Repayment	-10,000
13/11/2022	CUST567	1	Repayment	-2,000
18/11/2022	CUST567	1	Contract Sign	0
18/11/2022	CUST567	1	Repayment	-3,000

I'm using the following query to extract the above table:

Select
DateOfActivity, CustomerReferencce, ReferenceLine, Description, ReceivableAmount
From 'Table A'
Where
DateOfActivity >= '2022-09-01'
Group by
DateOfActivity

As you can see that the table will only get bigger because more customer activity is being added. How can I change my query so the customers who have fully paid their receivable amount don't show up in this table?

The result from the above script change that I am expecting is as follows:

DateOfActivity	CustomerReference	Reference Line	Description	Receivable Amount
24/10/2022	CUST567	1	Credit Purchase	20,000
24/10/2022	CUST555	2	Credit Purchase	50,000
27/10/2022	CUST555	2	Contract Sign	0
27/10/2022	CUST567	1	Contract Sign	0
13/11/2022	CUST567	1	Repayment	-10,000
13/11/2022	CUST567	1	Repayment	-2,000
18/11/2022	CUST567	1	Contract Sign	0
18/11/2022	CUST567	1	Repayment	-3,000

CUST567 Reference Line 4 has been removed because the sum of his Credit Purchase + Contract Sign + Repayment = $0. All other Customer rows are still showing up.

How can edit the query so this is done automatically for Large data? Please note the following assumptions:

• Customer Reference for multiple customers can be the same or different (for example in the above example, CUST567 has two Reference Lines 1 & 4. However, CUST555 only has one reference Line 2.

• The data is removed for Customers based on the Receivable amount coming down to Nil (so all rows for that CustomerReference & Reference Line are removed)

Thanks in Advance

Answer 1

What are you trying to achieve here? Are the references to large data because something is getting slow or something?

I'd suggest you'd want to make a view for your query (google the syntax for your flavour of SQL, but should be something like create view as {your query};)

Then you can query that view with an additional where "Receivable Amount !=0

You could also make a subquery, i.e select * from ({your query}) as x where x."Receivable Amount !=0

But if you've written the sql you've gotten to so far I assume you know that? So is there an issue in terms of performance or some such that's caused you to ask the question?

Answer 2

As I understand the question, we need to build the sum of the amounts grouped by the reference line. So this query will get those reference lines having this sum of amounts = 0:

SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0;

Then, we can use this query as a subquery and fetch all other entries not having such a reference line:

SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine NOT IN 
(SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0);

This will produce the expected outcome.

We could of course also use IN and amount <> 0 instead of NOT IN and amount = 0:

SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine IN 
(SELECT 
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) <> 0);

This will create the same result. Just take what you prefer.

Try out: db<>fiddle

An important note: In your question and comments, you are talking about the sum for the reference line is "nil" or "null". This is untrue. It is zero.

NULL or NIL would mean there is no amount, so this is something completely different!

All your amounts are NOT NULL and the sum of them is also NOT NULL, but zero for reference line 4.

EDIT: If it should also be grouped by CustomerReference, we can extend the previous queries like this:

SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE (CustomerReference, ReferenceLine) NOT IN 
(SELECT 
CustomerReference, ReferenceLine
FROM tableA
GROUP BY CustomerReference, ReferenceLine
HAVING SUM(ReceivableAmount) = 0);

OR

SELECT 
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE (CustomerReference, ReferenceLine) IN 
(SELECT 
CustomerReference, ReferenceLine
FROM tableA
GROUP BY CustomerReference, ReferenceLine
HAVING SUM(ReceivableAmount) <> 0);

Updated fiddle: db<>fiddle