Exclude Negative Values that Have the Same Absolute Values

Question

I have the table below

PolicyNumber    Premium Commission  DataSource  EffectiveDate
1              -2.25    -0.41       Internal    31/03/2018
1               160.26  29.14       Internal    31/03/2018
1                98.81  17.96       Internal    31/03/2018
2              361.24   65.67       Internal    31/01/2018
2              570.35   103.70      Internal    31/01/2018
2              -17.05   -3.10       Internal    31/01/2018
3              240.60   0.00        External    31/01/2018
3             196.64    0.00        External    31/01/2018
3             240.60    0.00        External    31/01/2018
3              196.64   0.00        External    31/01/2018
3              -240.60  0.00        External    28/02/2018
3            -196.64    0.00        External    28/02/2018
3            -240.60    0.00        External    28/02/2018
3            -196.64    0.00        External    28/02/2018
3             224.04    107.54      External    28/02/2018
3              176.97   84.95       External    28/02/2018
4             504.00    0.00        External    31/01/2018
4              68.33    0.00        External    31/01/2018
4              504.00   0.00        External    31/01/2018
4              68.33    0.00        External    31/01/2018
4            -504.00    0.00        External    28/02/2018
4             -68.33    0.00        External    28/02/2018
4            -504.00    0.00        External    28/02/2018
4             -68.33    0.00        External    28/02/2018
4             453.55    217.70      External    28/02/2018
4              61.44    29.49       External    28/02/2018

I need to exclude all the negative premium value which has the same absolute value from the previous one. For example, PolicyNumber 3, Premium 240.60 and 196.64 are adjusted by negative premium value -240.60 and -196.64. I need to exclude PolicyNumber 3.

I expect to have the end result like the table below.

PolicyNumber    Premium Commission  DataSource  EffectiveDate
1               -2.25   -0.41       Internal    31/03/2018
1               160.26  29.14       Internal    31/03/2018
1               98.81   17.96       Internal    31/03/2018
2               361.24  65.67       Internal    31/01/2018
2               570.35  103.70      Internal    31/01/2018
2               -17.05  -3.10       Internal    31/01/2018

I have tried the query below but it doesn't give me what I expect

    with cte as 
    ( select distinct a.PolicyNumber, a.datasource, a.EffectiveDate from InsuranceTable a 

inner join (select PolicyNumber, premium, EffectiveDate, datasource 

    from InsuranceTable) b on a.PolicyNumber = b.PolicyNumber  

    where a.premium < 0 and a.EffectiveDate = b.EffectiveDate and a.datasource = b.datasource and b.premium > 0   ) 

select x.PolicyNumber, x.premium, x.EffectiveDate, x.Commission, x.datasource from InsuranceTable x 
inner join cte y on x.PolicyNumber = y.PolicyNumber and x.datasource = y.datasource 
order by x.PolicyNumber, x.EffectiveDate

I'm using Microsoft SQL Server 2012. Thanks All for your help.

Answer 1

The CTE below finds all policies having a premium which is exactly cancelled out by a negative premium of the same amount. It does so by aggregating by policy and absolute value of the premium. Then, we only need a very simple query to obtain the policies you want to see.

WITH cte AS (
    SELECT DISTINCT PolicyNumber
    FROM InsuranceTable
    GROUP BY PolicyNumber, ABS(Premium)
    HAVING COUNT(*) > 1 AND MAX(Premium) <> MIN(Premium)
)

SELECT t1.*
FROM InsuranceTable t1
WHERE NOT EXISTS (SELECT 1 FROM cte t2 WHERE t1.PolicyNumber = t2.PolicyNumber);