skip to Main Content

Wondering if it’s possible to remove results with the same value.

I have a payments table containing ADJUSTMENTS and REFUNDS entered as AJUSTE and ESTORNO. While both can exist in an invoice, if the total sum of each are equal, they are to be ignored. If the sum of each are not equal, then will be considered for calculation.

I have come up with a list of each as follows:

select sum(pay_amount) as pay_amount, pay_type, pay_date, active, invoice_no, invoice_date 
from payments 
where pay_type = 'AJUSTE' 
    and pay_date like '%-%-2024' 
    and active = '1' 
    and pay_amount not in (
        select SUM(pay_amount) as pay_amount 
        from payments 
        where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no
    ) 
GROUP BY invoice_no;

enter image description here

Then this query for the opposite:

select sum(pay_amount) as pay_amount, pay_type, pay_date, active, invoice_no, invoice_date 
from payments 
where pay_type = 'ESTORNO' 
    and pay_date like '%-%-2024' 
    and active = '1' 
    and pay_amount not in (
        select SUM(pay_amount) as pay_amount 
        from payments 
        where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no
    ) 
GROUP BY invoice_no;

enter image description here

Although refunds are negative, I want to exclude all amounts that are the same and add the ones that are different.

Is this possible through a query in MySQL 5.7?

—- edit ——

I have created a fiddle with demo data: https://www.mycompiler.io/view/6qxcOxSlkuv

AJUSTE (Adjustements) are positive number in the DB meanwhile ESTORNO (refunds) are negative amounts.

When a refund is applied, an adjustment must follow in the same amount to zero out the balance on an invoice. There are scenarios where an adjustment can exist without a refund when the client doesn’t pay the full balance, correct an error, etc. In this case, we need to know the pay_amount and pay_type of this adjustment.

It is also possible that there may be 2 adjustments and only 1 refund. Ex. Client paid half the balance and a refund of that half payment was applied, plus a full adjustment to write-off the invoice.

The query must look at each individual invoice. When the sum of all refunds and adjustments within the same invoice exists and adds up to zero, we skip over that particular invoice. If the sum of adjustment is more than refund or vice versa, we want the pay_type (AJUSTE or ESTORNO) with the difference (pay_type). (ex. ESTORNO 100, AJUSTE 500. Must output AJUSTE 400).

The query you provided in the answer returns invoices 78 and 79.
Invoice 79 has ESTORNO 100 and AJUSTE 551.20 which is correct.
Invoice 78 has ESTORNO 100 and AJUSTE 200 which is also correct.

Invoice 55 has AJUSTE 2000 and doesn’t show up on the results.

Hope this all makes sense.

— second edit —–

If the sum() of AJUSTE and ESTORNO in an invoice_no is the same, it gets ignored – do not include in results.

If the sum() of either AJUSTE or ESTORNO in an invoice_no is different, show both results as a positive number.

Payment_id is the primary key. My apologies as the mysql fiddle didn’t include it.

Expected result from mysql fiddle:

enter image description here

2

Answers


  1. Join your subqueries and show only the rows that have different sums for the same invoice.

    SELECT e.*
    FROM (
        select sum(pay_amount) as pay_amount, pay_type, invoice_no
        from payments 
        where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no, pay_type
    ) AS e
    JOIN (
        select sum(pay_amount) as pay_amount, pay_type, invoice_no
        from payments 
        where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no, pay_type
    ) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount
    
    UNION ALL
    
    SELECT a.*
    FROM (
        select sum(pay_amount) as pay_amount, pay_type, invoice_no
        from payments 
        where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no, pay_type
    ) AS e
    JOIN (
        select sum(pay_amount) as pay_amount, pay_type, invoice_no
        from payments 
        where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
        group by invoice_no, pay_type
    ) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount
    

    DEMO

    To avoid ONLY_FULL_GROUP_BY errors you have to remove the pay_date, active, and invoice_date columns from the SELECT list, since they may not be unique within the groups.

    Login or Signup to reply.
  2. See another example

    Condition When the sum of all refunds and adjustments within the same invoice exists and adds up to zero, we skip over that particular invoice.

    So first step – Calculation sum for pay_type in (‘AJUSTE’,’ESTORNO’) for every invoice_no.
    We take invoices, where total sum is not <>0.

    Subquery.

    select invoice_no
      ,sum(pay_amount) sum_AE
    from payments p
    where pay_type in('AJUSTE','ESTORNO') and pay_date like '%-%-2024' 
         and active = '1'
    group by invoice_no
    having abs(sum(pay_amount))>0.001
    
    invoice_no sum_AE
    55 2000
    78 100
    79 451.20000000000005

    Full query.
    Inner join previous output with payments table

    select p.invoice_no,abs(pay_amount) pay_amount,pay_type
    from payments p
    inner join(
       select invoice_no 
         ,sum(pay_amount) sum_AE
       from payments p
       where pay_type in('AJUSTE','ESTORNO') and pay_date like '%-%-2024' 
           and active = '1'
       group by invoice_no
       having abs(sum(pay_amount))>0.001
     )c on p.invoice_no=c.invoice_no
    where pay_type in('AJUSTE','ESTORNO')
    order by p.invoice_no ;
    
    invoice_no pay_amount pay_type
    55 2000 AJUSTE
    78 200 AJUSTE
    78 100 ESTORNO
    79 551.2 AJUSTE
    79 100 ESTORNO

    fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search