By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA (DateOfActivity date, CustomerReference varchar(100),
ReferenceLine int,
Description varchar(100), ReceivableAmount int);
insert into tableA values
('2022-10-24','CUST567',1,'Credit Purchase',20000),
('2022-10-24','CUST567',4,'Credit Purchase',10000),
('2022-10-24','CUST555',2,'Credit Purchase',50000),
('2022-10-27','CUST555',2,'Contract Sign',0),
('2022-10-27','CUST567',4,'Contract Sign',0),
('2022-10-27','CUST567',1,'Contract Sign',0),
('2022-10-27','CUST567',4,'Repayment',-3500),
('2022-10-27','CUST567',4,'Repayment',-6500),
('2022-11-13','CUST567',1,'Repayment',-10000),
('2022-11-13','CUST567',1,'Repayment',-2000),
('2022-11-18','CUST567',1,'Contract Sign',0),
('2022-11-18','CUST567',1,'Repayment',-3000)
Records: 12 Duplicates: 0 Warnings: 0
SELECT * FROM tableA;
| DateOfActivity | CustomerReference | ReferenceLine | Description | ReceivableAmount |
|---|---|---|---|---|
| 2022-10-24 | CUST567 | 1 | Credit Purchase | 20000 |
| 2022-10-24 | CUST567 | 4 | Credit Purchase | 10000 |
| 2022-10-24 | CUST555 | 2 | Credit Purchase | 50000 |
| 2022-10-27 | CUST555 | 2 | Contract Sign | 0 |
| 2022-10-27 | CUST567 | 4 | Contract Sign | 0 |
| 2022-10-27 | CUST567 | 1 | Contract Sign | 0 |
| 2022-10-27 | CUST567 | 4 | Repayment | -3500 |
| 2022-10-27 | CUST567 | 4 | Repayment | -6500 |
| 2022-11-13 | CUST567 | 1 | Repayment | -10000 |
| 2022-11-13 | CUST567 | 1 | Repayment | -2000 |
| 2022-11-18 | CUST567 | 1 | Contract Sign | 0 |
| 2022-11-18 | CUST567 | 1 | Repayment | -3000 |
SELECT
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0;
| ReferenceLine |
|---|
| 4 |
SELECT
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine NOT IN
(SELECT
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) = 0);
| DateOfActivity | CustomerReference | ReferenceLine | Description | ReceivableAmount |
|---|---|---|---|---|
| 2022-10-24 | CUST567 | 1 | Credit Purchase | 20000 |
| 2022-10-24 | CUST555 | 2 | Credit Purchase | 50000 |
| 2022-10-27 | CUST555 | 2 | Contract Sign | 0 |
| 2022-10-27 | CUST567 | 1 | Contract Sign | 0 |
| 2022-11-13 | CUST567 | 1 | Repayment | -10000 |
| 2022-11-13 | CUST567 | 1 | Repayment | -2000 |
| 2022-11-18 | CUST567 | 1 | Contract Sign | 0 |
| 2022-11-18 | CUST567 | 1 | Repayment | -3000 |
SELECT
DateOfActivity, CustomerReference, ReferenceLine,
Description, ReceivableAmount
FROM tableA
WHERE ReferenceLine IN
(SELECT
ReferenceLine
FROM tableA
GROUP BY ReferenceLine
HAVING SUM(ReceivableAmount) <> 0);
| DateOfActivity | CustomerReference | ReferenceLine | Description | ReceivableAmount |
|---|---|---|---|---|
| 2022-10-24 | CUST567 | 1 | Credit Purchase | 20000 |
| 2022-10-24 | CUST555 | 2 | Credit Purchase | 50000 |
| 2022-10-27 | CUST555 | 2 | Contract Sign | 0 |
| 2022-10-27 | CUST567 | 1 | Contract Sign | 0 |
| 2022-11-13 | CUST567 | 1 | Repayment | -10000 |
| 2022-11-13 | CUST567 | 1 | Repayment | -2000 |
| 2022-11-18 | CUST567 | 1 | Contract Sign | 0 |
| 2022-11-18 | CUST567 | 1 | Repayment | -3000 |