SQL - Need to find duplicate records but EXCLUDE reversed transactions

Question

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 creating a negative value (but the original duplicate still remains due to regulatory requirements). I'd like to create a SQL query (and use Crystal Reports) to make a report for the admins to easily find duplicate transactions. Due to the mass quantity of transactions, I'd like to make it easier for them by disregarding transactions that they have already reversed.

Here's an example of what I'd like to do:

Transaction Date ; Transaction Qty ; Transaction Value ; Reversal

1/1/08    ; 14    ;    70.00    ; N
1/1/08    ; 14    ;    70.00    ; N
1/1/08    ; -14   ;    -70.00   ; Y
2/1/08    ; 17    ;    89.00    ; N
2/15/08   ; 18    ;    95.00    ; N
2/15/08   ; 18    ;    95.00    ; N
3/1/08    ; 11    ;    54.00    ; N
3/1/08    ; -11   ;    -54.00   ; Y
3/1/08    ; 11    ;    54.00    ; N
3/1/08    ; 11    ;    54.00    ; N
3/1/08    ; 11    ;    54.00    ; N

Ideally, if I ran my "desired" query on the table above, I would receive the following result:

Transaction Date ; Transaction Qty ; Transaction Value ; Count

2/15/08    ; 18    ;    95.00    ; 2
3/1/08     ; 11    ;    54.00    ; 3

Does that make sense? I've already figured out how to write the query to give me a count of duplicates, but I can't figure out how to exclude duplicate records which have been "backed out" already. Any help would be greatly appreciated!

Answer 1

How about:

select dt, abs(qty), abs(val),
       sum(case when reversal='Y' then -1 else 1 end) as count
from transactions
group by dt, abs(qty), abs(val)
having sum(case when reversal='Y' then -1 else 1 end) > 1;

I have just tested it in Oracle and it works:

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

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

SQL> select dt, abs(qty), abs(val),
  2         sum(case when reversal='Y' then -1 else 1 end) as count
  3  from transactions
  4  group by dt, abs(qty), abs(val)
  5  having sum(case when reversal='Y' then -1 else 1 end) > 1;

DT            ABS(QTY)   ABS(VAL)      COUNT
----------- ---------- ---------- ----------
15-FEB-2008         18         95          2
01-MAR-2008         11         54          3