Return only records that net to zero

Transact-SQL
1 
DECLARE @test AS TABLE
(
ID VARCHAR (2),
PolRef VARCHAR (7),
Customer VARCHAR (50),
Agent VARCHAR (50),
Product VARCHAR (2),
TDate VARCHAR (8),
Value DECIMAL (15, 0)
)
INSERT INTO @test
VALUES 
(
'AP', '1234567', 'Mr John Smith', 'Miss Tina Jones', 'PC', '20181201', '50.00'
)
INSERT INTO @test
VALUES 
(
'MF', '1234567', 'Mr John Smith', 'Miss Tina Jones', 'PC', '20181201', '25.00'
)
INSERT INTO @test
VALUES 
(
'DD', '1234567', 'Mr John Smith', 'Miss Tina Jones', 'PC', '20181201', '-75.00'
)
INSERT INTO @test
VALUES 
(
'AP', '9000067', 'Mr Peter Jackson', 'Miss Julie Davidson', 'PC', '20181201', '50.00'
)
INSERT INTO @test
VALUES 
(
'MF', '9000067', 'Mr Peter Jackson', 'Miss Julie Davidson', 'PC', '20181201', '50.00'
)
INSERT INTO @test
VALUES 
(
'DD', '9000067', 'Mr Peter Jackson', 'Miss Julie Davidson', 'PC', '20181201', '-50.00'
)

SELECT * FROM @test

I would like to return only records with the same PolRef and TDate where the sum of all transactions nets to zero.

In the above example, Polref 1234567 has $75 in AP and MF transactions and a -$75 in DD transaction; this nets off to zero so I'd want him returned.

The other record 9000067 nets to $50 so I wouldn't be interested in that one.

Any pointers appreciated.

2

Sum transactions
Group by PolRef and TDate
Having sum transactions = 0

3

Select PolRef,TDate, sum( transactions)
From @test
Group by PolRef , TDate
Having sum( transactions ) = 0

1 Like