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.
2
Answers
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:
ASSUMING:
MODIFICATIONS:
My Fiddle example:
https://dbfiddle.uk/r7Fkh6zW
Giving us:
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
Recognizing this is mySQL 5.7 there are no Common table expressions: So I’m stuck using inline views.
Things to note:
Giving us: This https://dbfiddle.uk/OcdGkhbP
With these results:
Note some of the logic changes:
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.