Sql - Need To Find Duplicate Records But Exclude Reversed Transactions

I have a table of transactions which will occasionally have duplicate entries. If/When an admin finds these duplicate entries, they will reverse the transactions, therefore creatin

Solution 1:

How about:

select dt, abs(qty), abs(val),
       sum(casewhen reversal='Y'then-1else1end) as count
from transactions
groupby dt, abs(qty), abs(val)
havingsum(casewhen reversal='Y'then-1else1end) >1;

I have just tested it in Oracle and it works:

createtable transactions
( dt date
, qty number
, val number
, reversal varchar2(1)
);

insertinto transactions values (to_date('1/1/08','mm/dd/yy')    , 14    ,    70.00    , 'N');
insertinto transactions values (to_date('1/1/08','mm/dd/yy')    , 14    ,    70.00    , 'N');
insertinto transactions values (to_date('1/1/08','mm/dd/yy')    , -14   ,    -70.00   , 'Y');
insertinto transactions values (to_date('2/1/08','mm/dd/yy')    , 17    ,    89.00    , 'N');
insertinto transactions values (to_date('2/15/08','mm/dd/yy')   , 18    ,    95.00    , 'N');
insertinto transactions values (to_date('2/15/08','mm/dd/yy')   , 18    ,    95.00    , 'N');
insertinto transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');
insertinto transactions values (to_date('3/1/08','mm/dd/yy')    , -11   ,    -54.00   , 'Y');
insertinto transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');
insertinto transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');
insertinto transactions values (to_date('3/1/08','mm/dd/yy')    , 11    ,    54.00    , 'N');

SQL>select dt, abs(qty), abs(val),
  2sum(casewhen reversal='Y'then-1else1end) as count
  3from transactions
  4groupby dt, abs(qty), abs(val)
  5havingsum(casewhen reversal='Y'then-1else1end) >1;

DT            ABS(QTY)   ABS(VAL)      COUNT
----------- ---------- ---------- ----------15-FEB-20081895201-MAR-200811543