Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select from 2 tables in the last 30 days, return 0 if null/not exist

I’m trying to obtain data from 2 tables in the last 30 days in MySQL 5.7 to create a graph. The idea is to display the invoice total and payment total per day in a bar graph for comparison. Therefore I need the date of transaction (for payment and/or invoice) and totals for that day.

Need to select the following:
INVOICES table

  • Sum of invoice_total per invoice_date

PAYMENTS table

  • Sum of pay_amount per pay_date

There are entries in the PAYMENTS table where the date doesn’t exist in the INVOICES table and vice versa. When we come across these it should return a 0 amount.

select STR_TO_DATE(payments.pay_date,'%d-%m-%Y') AS TransDate, ROUND(sum(payments.pay_amount),2) as PmtTOTAL, ROUND(sum(invoices.invoice_total),2) as InvTOTAL 

FROM invoices

LEFT JOIN payments on invoices.invoice_no = payments.invoice_no 

WHERE payments.active='1' and invoices.active='1' and payments.pay_type <> 'Desconto' and payments.pay_type <> 'AJUSTE' and payments.pay_type <> 'ESTORNO' 
and STR_TO_DATE(payments.pay_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 

GROUP BY invoices.invoice_date 

ORDER BY payments.pay_date asc;

Here’s my fiddle: https://dbfiddle.uk/IjLM7Kyn

PS: fiddle is getting a group by error which I’m not getting locally.

Thanks in advance!

The query provided by xQbert has allowed me to come up with a better graph, reason why it was never posted initially. Basically this graph gives the user a quick overview of all billings and payments per day in the last 30 days. xQbert’s query almost has it.
enter image description here

Tags:

2

Answers


  1. 0

    Reason for Error in dbfiddle:
    The server is setup to use explicit notations must use ‘as’ when aliasing, must have all items in select not aggregated in the group by etc.. you don’t have such restrictions on your environment.

    QUESTIONS:

    • If there is no payment; what date should be displayed? Since you’re pulling payments and you may have a invoice without a payment
    • Since multiple payments may occur on an invoice we need max of invoice amount since there are not MULTIPLE invoices with the same number.
    • why do you expect the payment total for 9/16/ to be 850? There are no invoices in payment table with invoice number 51.

    ASSUMING:

    1. Invoice is UNIQUE and must always exist before a payment

    MODIFICATIONS:

    1. Changed date to invoice date instead of payment date on select.
    2. used max on invoice total a sum will result in amount*number of payments
    3. used coalesce on payments incase there are 0 amounts to replace the NULL
    4. Modified where clause/join criteria. As it stood your where clause was negating the left join making it behave like an inner join.
    5. Modified pay_type to use not in instead of multiple <>’s
    6. group by invoices.invoice_date.

    My Fiddle example:
    https://dbfiddle.uk/r7Fkh6zW

    SELECT 
       -- Using invoice date since payment date may not exist.
       STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y') AS fmtDate
     , ROUND(max(invoices.invoice_total),2) as INVTOTAL 
       -- using coalesce to show 0 instead of NULL 
       -- using max since an invoice can only have 1 amount and fanning of records will cause a 
       -- SUM to increase invoice total. 
     , coalesce(ROUND(sum(payments.pay_amount),2),0) as TOTAL
FROM  invoices 
LEFT JOIN payments 
  on invoices.invoice_no = payments.invoice_no 
  -- Moved these limits to the join so as left join doesn't behave like inner join
  and payments.active='1' 
  and payments.pay_type not in ('Desconto', 'AJUSTE', 'ESTORNO')
  and STR_TO_DATE(payments.pay_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
WHERE invoices.active='1'   
GROUP BY invoices.invoice_date

    Giving us:

    +------------+----------+---------+
|  fmtDate   | INVTOTAL |  TOTAL  |
+------------+----------+---------+
| 2024-09-02 |    90.00 |  830.00 |
| 2024-09-03 |    90.00 |  940.00 |
| 2024-09-04 |   320.00 |  590.00 |
| 2024-09-05 |   340.00 |  780.00 |
| 2024-09-06 |    45.00 |  235.00 |
| 2024-08-07 |   150.00 |    0.00 |
| 2024-08-08 |   120.00 |    0.00 |
| 2024-08-09 |   380.00 |    0.00 |
| 2024-09-09 |   240.00 |  420.00 |
| 2024-09-10 |   400.00 |  500.00 |
| 2024-09-12 |   250.00 |  250.00 |
| 2024-09-16 |   440.00 |    0.00 |
| 2024-08-20 |   450.00 | 1050.00 |
| 2024-08-21 |    50.00 |  280.00 |
| 2024-08-22 |    50.00 |  450.00 |
| 2024-08-23 |   230.00 |  830.00 |
| 2024-08-26 |   490.00 |  650.00 |
| 2024-08-28 |    60.00 |  680.00 |
| 2024-08-29 |   110.00 |  110.00 |
+------------+----------+---------+

    Unsure what expectations are so I don’t know how to validate these changes.

    And now if we update payments invoice number for 10,9,8 to be 51 as indicated in comments you can see the 850. so was it bad data?

    https://dbfiddle.uk/6Q6aoQ2w

    Login or Signup to reply.
  2. 0

    Recognizing this is mySQL 5.7 there are no Common table expressions: So I’m stuck using inline views.

    Things to note:

    1. Summed each table’s data by date and amount
    2. then since there are missing dates on both tables; we need to FULL OUTER JOIN; but that too isn’t an option so we use Payments LEFT JOIN Invoices UNION INVOICES LEFT JOIN Payments to get all records back

    Giving us: This https://dbfiddle.uk/OcdGkhbP

    SELECT PAYDATE TRANSDATE, TOTAL, INVTOTAL
FROM (SELECT STR_TO_DATE(payments.pay_date,'%d-%m-%Y') AS PAYDATE
     , ROUND(SUM(payments.pay_amount),2) as TOTAL
  FROM payments
  WHERE payments.active='1' 
    and payments.pay_type not in ('Desconto', 'AJUSTE', 'ESTORNO')
    and STR_TO_DATE(payments.pay_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
  GROUP BY STR_TO_DATE(payments.pay_date,'%d-%m-%Y')
  )  P
LEFT JOIN (
  SELECT STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y') AS INVDATE
       , ROUND(SUM(invoices.invoice_total),2) as INVTOTAL
  FROM invoices
  WHERE invoices.active='1' 
    and STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
  GROUP BY STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y')
  ) I
on P.PAYDATE=I.INVDATE

UNION ALL 

SELECT INVDATE TRANSDATE, TOTAL, INVTOTAL
FROM (SELECT STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y') AS INVDATE
       , ROUND(SUM(invoices.invoice_total),2) as INVTOTAL
  FROM invoices
  WHERE invoices.active='1' 
     and STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
  GROUP BY STR_TO_DATE(invoices.invoice_date,'%d-%m-%Y')
  ) I2
LEFT JOIN 
  (SELECT STR_TO_DATE(payments.pay_date,'%d-%m-%Y') AS PAYDATE
     , ROUND(SUM(payments.pay_amount),2) as TOTAL
  FROM payments
  WHERE payments.active='1' 
    and payments.pay_type not in ('Desconto', 'AJUSTE', 'ESTORNO')
    and STR_TO_DATE(payments.pay_date,'%d-%m-%Y') between (CURDATE() - INTERVAL 1 MONTH ) and CURDATE() 
  GROUP BY STR_TO_DATE(payments.pay_date,'%d-%m-%Y')
  ) P2
   on I2.INVDATE=P2.PAYDATE
 WHERE P2.PAYDATE is null

    With these results:

    +------------+---------+----------+
| TRANSDATE  |  TOTAL  | INVTOTAL |
+------------+---------+----------+
| 2024-08-20 |  200.00 | 1050.00  |
| 2024-08-21 |  280.00 | 280.00   |
| 2024-08-22 |  450.00 | 450.00   |
| 2024-08-23 |  830.00 | 830.00   |
| 2024-08-26 |  200.00 | 690.00   |
| 2024-08-27 |  450.00 | null     |
| 2024-08-28 |  680.00 | 680.00   |
| 2024-08-29 |  110.00 | 110.00   |
| 2024-09-02 |  830.00 | 830.00   |
| 2024-09-03 |  940.00 | 940.00   |
| 2024-09-04 |  300.00 | 610.00   |
| 2024-09-05 | 1070.00 | 790.00   |
| 2024-09-06 |  235.00 | 235.00   |
| 2024-09-09 |  420.00 | 420.00   |
| 2024-09-10 |  500.00 | 500.00   |
| 2024-09-12 |  250.00 | 250.00   |
| 2024-09-16 |  850.00 | 440.00   |
+------------+---------+----------+

    Note some of the logic changes:

    • We filtered by invoice date for 1 month (Differnet)
    • we filtered payments by date for 1 month (Same)
    • we join based on date not invoice number (Different)
    • We display payment date if one exists, otherwise we use invoice date. (combining invoice/payment in one column if no payment different)

    So the question becomes:
    What invoice/payments are you after when referencing date:
    either within the past 30 days;
    only those with a payment in the past 30 days which excludes invoices without payments then.

    Unable to use Common table expressions due to version of MySQL (Supported in 8) which would make this MUCH easier to read.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search