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

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 above script change that I am expecting is as follows:

CUST567 Reference Line 4 has been removed because the sum of his Credit Purchase Contract Sign Repayment = $0. All other Customers 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 same or different (for example in above example, CUST567 has two Reference Line 1 & 4. However, CUST555 only has one reference Line 2.

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

Thanks in Advance

CodePudding user response：

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?

CodePudding user response：

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.