# Removing pairs of transactions

I am attempting to remove transactions that have been reversed from a table. the table has Account, Date, Amount and Row. If a transaction has been reversed Account will match and Amount will be inverse of each other.

Example Table

``````Account    Date     Amount   Row
12         1/1/18   45       72    -- Case 1
12         1/2/18   50       73
12         1/2/18  -50       74
12         1/3/18   52       75

15         1/1/18   51       76    -- Case 2
15         1/2/18   51       77
15         1/2/18  -51       78
15         1/2/18   51       79

18         1/2/18   50       80    -- Case 3
18         1/2/18   50       81
18         1/2/18  -50       82
18         1/2/18  -50       83
18         1/3/18   50       84
18         1/3/18   50       85

20         1/1/18   57       88    -- Case 4
20         1/2/18   57       89
20         1/4/18  -57       90
20         1/5/18   57       91
``````

Desired Results Table

``````Account    Date     Amount   Row
12         1/1/18   45       72    -- Case 1
12         1/3/18   52       75

15         1/1/18   51       76    -- Case 2
15         1/2/18   51       79

18         1/3/18   50       84    -- Case 3
18         1/3/18   50       85

20         1/1/18   57       88    -- Case 4
20         1/5/18   57       91
``````

Removing all instances of inverse transactions does not work when there are multiple transactions when all other columns are the same. My attempt was to count all duplicate transactions, count of all inverse duplicate transactions, subtracting those to get the number of rows I needed from each transactions group. I was going to pull the first X rows but found in most cases I want the last X rows of each group, or even a mix (the first and last in Case 2).

I either need a method of removing pairs from the original table, or working from what I have so far, a method of distinguishing which transactions to pull.

Code so far:

``````--adding row Numbers
with a as (
select
account a,
date d,
amount f,
row_number() over(order by account, date) r
from table),

--counting Duplicates
b as (
select a.a, a.f, Dups
from a join (
select a, f, count(*) Dups
from a
group by a.a, a.f
having count(*)>1
) b
on a.a=b.a and
b.f=a.f
where a.f>0
),

--counting inverse duplicates
c as (
select a.a, a.f, InvDups
from a join (
select a, f, count(*) InvDups
from a
group by a.a, a.f
having count(*)>1
) b
on a.a=b.a and
-b.f=a.f
where a.f>0
),

--combining c and d to get desired number of rows of each transaction group
d as (
select
b.a, b.f, dups, InvDups, Dups-InvDups TotalDups
from b join c
on b.a=c.a and
b.f=c.f
),

--getting the number of rows from the beginning of each transaction group
select d.a, d.d, d.f
from
(select
a, d, f, row_number() over (group by a, d, f) r2
from a) e
join d
on e.a=d.a and
TotalDups<=r2
``````
-
I'm not removing the records from the table itself, instead I'm using this as a temp table to run a query to see if there have been 3 or more transactions within a rolling 1 year period for any given account. – Golden Ratio

The following handles your three cases:

``````with t as (
select t.*,
row_number() over (partition by account, date, amount order by row) as seqnum
from table t
)
select t.*
from t
where not exists (select 1
from t t2
where t2.account = t.account and t2.date = t.date and
t2.amount = -t.amount and t2.seqnum = t.seqnum
);
``````
-
I'm getting an error, Invalid column name row, but the table has the column row in it. I am unsure how to alter your suggestion to remove the error. – Golden Ratio
@GoldenRatio . . . I don't think `row` is a reserved word in SQL Server 2008. Perhaps it is case sensitive or try `[Row]`. – Gordon Linoff
I tried substituting row with trow in all cases, I also tried [row], both showed the same error. – Golden Ratio

Use This

``````;WITH CTE
AS
(
SELECT
[Row]
FROM YourTable YT
WHERE Amount > 0
AND EXISTS
(
SELECT 1 FROM YourTable WHERE Account = YT.Account
AND [Date] = YT.[Date]
AND (Amount+YT.Amount)=0
)
UNION ALL
SELECT
[Row]
FROM YourTable YT
WHERE Amount < 0
AND EXISTS
(
SELECT 1 FROM YourTable WHERE Account = YT.Account
AND [Date] = YT.[Date]
AND (Amount+YT.Amount)>0
)
)
SELECT * FROM YourTable
WHERE EXISTS
(
SELECT 1 FROM CTE WHERE [Row] = YourTable.[Row]
)
``````
-
Unfortunately, the reversal transactions are not always on the same date. I should have been more varied with my cases. I will add that as an addition case in the question. I was able to have the query produce results, but there are pairs on the same dates it does not remove – Golden Ratio
In that case, Just remove the Check for Date in the Exists statement. And it should work as expected – Jayasurya Satheesh
I tried with and without the date before I posted my comment. Neither worked. – Golden Ratio

You can try this.

``````SELECT T_P.* FROM
( SELECT *, ROW_NUMBER() OVER(PARTITION BY Account, Amount ORDER BY [Row] ) RN from @MyTable WHere Amount > 0 ) T_P
LEFT JOIN
( SELECT *, ROW_NUMBER() OVER(PARTITION BY Account, Amount ORDER BY [Row] ) RN from @MyTable WHere Amount < 0 ) T_N
ON T_P.Account = T_N.Account
AND T_P.Amount = ABS(T_N.Amount)
AND T_P.RN = T_N.RN
WHERE
T_N.Account IS NULL
``````
-
This works! Thank you! – Golden Ratio