code_junkie

SQL - Need to find duplicate records but EXCLUDE reversed transactions

Nov 12th, 2011
52
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.18 KB | None | 0 0
  1. 1/1/08 ; 14 ; 70.00 ; N
  2. 1/1/08 ; 14 ; 70.00 ; N
  3. 1/1/08 ; -14 ; -70.00 ; Y
  4. 2/1/08 ; 17 ; 89.00 ; N
  5. 2/15/08 ; 18 ; 95.00 ; N
  6. 2/15/08 ; 18 ; 95.00 ; N
  7. 3/1/08 ; 11 ; 54.00 ; N
  8. 3/1/08 ; -11 ; -54.00 ; Y
  9. 3/1/08 ; 11 ; 54.00 ; N
  10. 3/1/08 ; 11 ; 54.00 ; N
  11. 3/1/08 ; 11 ; 54.00 ; N
  12.  
  13. 2/15/08 ; 18 ; 95.00 ; 2
  14. 3/1/08 ; 11 ; 54.00 ; 3
  15.  
  16. select dt, abs(qty), abs(val),
  17. sum(case when reversal='Y' then -1 else 1 end) as count
  18. from transactions
  19. group by dt, abs(qty), abs(val)
  20. having sum(case when reversal='Y' then -1 else 1 end) > 1;
  21.  
  22. create table transactions
  23. ( dt date
  24. , qty number
  25. , val number
  26. , reversal varchar2(1)
  27. );
  28.  
  29. insert into transactions values (to_date('1/1/08','mm/dd/yy') , 14 , 70.00 , 'N');
  30. insert into transactions values (to_date('1/1/08','mm/dd/yy') , 14 , 70.00 , 'N');
  31. insert into transactions values (to_date('1/1/08','mm/dd/yy') , -14 , -70.00 , 'Y');
  32. insert into transactions values (to_date('2/1/08','mm/dd/yy') , 17 , 89.00 , 'N');
  33. insert into transactions values (to_date('2/15/08','mm/dd/yy') , 18 , 95.00 , 'N');
  34. insert into transactions values (to_date('2/15/08','mm/dd/yy') , 18 , 95.00 , 'N');
  35. insert into transactions values (to_date('3/1/08','mm/dd/yy') , 11 , 54.00 , 'N');
  36. insert into transactions values (to_date('3/1/08','mm/dd/yy') , -11 , -54.00 , 'Y');
  37. insert into transactions values (to_date('3/1/08','mm/dd/yy') , 11 , 54.00 , 'N');
  38. insert into transactions values (to_date('3/1/08','mm/dd/yy') , 11 , 54.00 , 'N');
  39. insert into transactions values (to_date('3/1/08','mm/dd/yy') , 11 , 54.00 , 'N');
  40.  
  41. SQL> select dt, abs(qty), abs(val),
  42. 2 sum(case when reversal='Y' then -1 else 1 end) as count
  43. 3 from transactions
  44. 4 group by dt, abs(qty), abs(val)
  45. 5 having sum(case when reversal='Y' then -1 else 1 end) > 1;
  46.  
  47. DT ABS(QTY) ABS(VAL) COUNT
  48. ----------- ---------- ---------- ----------
  49. 15-FEB-2008 18 95 2
  50. 01-MAR-2008 11 54 3
Add Comment
Please, Sign In to add comment