Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
- 2/15/08 ; 18 ; 95.00 ; 2
- 3/1/08 ; 11 ; 54.00 ; 3
- 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;
- 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
Add Comment
Please, Sign In to add comment