I have a MySQL query with four IF statements that should set an alias to '1' or '0' according to the conditions inside. Although I don't have any errors, the only IF that's returning correct values is the one with the is_paid alias.
I double checked the conditions for the other three aliases and even if they are ok, I still get bogus results.
Am I doing something wrong? Is there any way I could write the query so I can get the expected results?
Here's the query:
SELECT r.doc_number,
r.doc_date,
r.due_date,
r.currency,
r.amount,
r.vat,
r.vatammount,
(r.amount + r.vatammount) final_amount,
r.currency,
b.boq_id,
b.boq_comp_id,
b.boq_client_id,
b.boq_agency,
b.boq_date,
b.boq_orders,
b.receivable_id,
c.comp_name,
crm.`cn-name-first`,
crm.`cn-name-last`,
bi.inv_path,
(SELECT SUM(amount_recieved) FROM receivables_payments WHERE r_id = b.receivable_id) total_amount_received,
IF (r.amount + r.vatammount = (SELECT SUM(amount_recieved) FROM receivables_payments WHERE r_id = b.receivable_id), '1', '0') AS is_paid,
IF (CURRENT_DATE >= r.due_date AND r.amount + r.vatammount != (SELECT SUM(amount_recieved) FROM receivables_payments WHERE r_id = b.receivable_id), '1', '0') AS is_overdue,
IF (r.due_date < CURRENT_DATE AND r.amount + r.vatammount != (SELECT SUM(amount_recieved) FROM receivables_payments WHERE r_id = b.receivable_id), '1', '0') AS is_outstanding,
IF (r.due_date = CURRENT_DATE AND r.amount + r.vatammount != (SELECT SUM(amount_recieved) FROM receivables_payments WHERE r_id = b.receivable_id), '1', '0') AS is_due_today
FROM receivables r
LEFT JOIN boq b ON b.receivable_id = r.id
LEFT JOIN boq_invoices bi ON bi.inv_boq_id = b.boq_id
LEFT JOIN comp_companies c ON c.comp_id = b.boq_comp_id
LEFT JOIN crm_contacts crm ON crm.contact_id = b.boq_client_id
WHERE r.status = 'active'
AND r.doc_type = 'inv'
AND b.boq_status = 'active'
GROUP BY r.id
HAVING is_due_today = '1'
ORDER BY r.doc_date DESC
LIMIT 10
Aucun commentaire:
Enregistrer un commentaire