SQL query to Ignore matching positive and negative values in a table-CodePudding

I have a transaction table that stores amount paid( amount) and corrected (-ve amount). I am looking for a query that would ignore a positive and a negative matching value of the amount for a date and post the sum of remaining number of transactions ignoring the 2 .

Id  Dept  Date         Amount
  1    A    21-Apr-21    1100
  1    A    21-Apr-21    1100
  1    A    21-Apr-21    -1100 
  1    A    07-Apr-21    1100
  1    A    03-Feb-21     100
  1    A    12-Jan-21     500

The sql query should ignore Rows 2 and 3 as the amount was corrected and should not be counted as a transaction.

o/p should be

 Id  Dept    sum(Amount) count(transaction)
  1    A           2800   4

CodePudding user response：

Maybe some idea like this could work.

SELECT Id, Dept, Date, Amount, COUNT(*) AS RecordCount
INTO #temptable
FROM table GROUP BY ...

SELECT
    t1.Id
    ,t1.Dept
    ,t1.Date
    ,(t1.RecordCount - COALESCE(t2.RecordCount, 0)) * t1.Amount
    ,t1.RecordCount - COALESCE(t2.RecordCount, 0)
FROM #temptable t1
    LEFT JOIN #temptable t2 ON
        t1.Id = t2.Id
        AND t1.Dept = t2.Dept
        AND t1.Date = t2.Date
        AND (t1.Amount * -1) = t2.Amount

CodePudding user response：

If I got you well, you can use below solution for that purpose. I first ranked all the occurrences of the same amount value, before I grouped them in order to make oracle ignore all matching positive and negative values.

with YourSample (Id, Dept, Date#, Amount) as (
select 1, 'A', to_date('21-Apr-21', 'dd-Mon-RR', 'nls_date_language=english'), 1100 from dual union all
select 1, 'A', to_date('21-Apr-21', 'dd-Mon-RR', 'nls_date_language=english'), 1100 from dual union all
select 1, 'A', to_date('21-Apr-21', 'dd-Mon-RR', 'nls_date_language=english'), -1100 from dual union all 
select 1, 'A', to_date('07-Apr-21', 'dd-Mon-RR', 'nls_date_language=english'), 1100 from dual union all
select 1, 'A', to_date('03-Feb-21', 'dd-Mon-RR', 'nls_date_language=english'),  100 from dual union all
select 1, 'A', to_date('12-Jan-21', 'dd-Mon-RR', 'nls_date_language=english'),  500 from dual
)
, ranked_rws as (
select Id, Dept, Date#
    , abs(Amount)Amount
    , sign(AMOUNT) row_sign
    , row_number() OVER (PARTITION BY Id, Dept, Amount order by date#, rownum) rn
from YourSample t 
)
, ingored_matched_pos_neg_values as (
select ID, DEPT, sum(row_sign) * AMOUNT AMOUNT/*, sum(row_sign)*/
from ranked_rws
group by ID, DEPT, AMOUNT, RN
having sum(row_sign) != 0  /* this line filters out all matching positive 
and negatives values (equality in terms of occurrences)*/
)
select ID, DEPT, sum(AMOUNT) sum,  count(*) transactions
from ingored_matched_pos_neg_values
group by ID, DEPT
;

demo