lundi 19 septembre 2016

MySQL query with multiple IF statements not returning the expected results

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