Hi guys, i have a problem here in my sql query. Before my query is to slow to run so i redo it and i successfully run it much faster than the original. My problem now is the data or total summary of the month is not the same with the total of my excel.
The total i get from my sql query is
14,960,304.41
The total that i have from excel is
15,212,500.00
I tried using EXPLAIN and this is my result
TABLE
tin
ti
po
tp
TYPE
ALL
eq_ref
eq_ref
eq_ref
NULL
PRIMARY
PRIMARY,tertiary
PRIMARY
rows
20248
1
1
1
filtered
100
100
100
100
Extra
Using where; Using temporary; Using filesort
Using where
Using where
Using where
What I have tried:
SELECT IFNULL(tp.remarks, '') AS remarks, tin.qty, IFNULL(tin.unit_description, '') AS unit_description,
IFNULL(tp.description, '') AS description,
tin.unit_price,
tin.amount,
IFNULL(ti.supplier, '') AS supplier,
IFNULL(po.remarks, '') AS remarks,
IFNULL(ti.invoice_number, '') AS invoice_number,
IFNULL(ti.wrr_number, '') AS wrr_number,
IFNULL(po.po_date, '') AS po_date,
IFNULL(ti.si_date, '') AS si_date,
IFNULL(po.po_locations, '') AS po_locations,
IFNULL(ti.remarks, '') AS remarks
FROM gen.purchase_order po
JOIN gen.tbl_invoiceheadersup ti ON po.id = ti.po_id
JOIN gen.tbl_invoicedetailssup tin ON ti.id = tin.invoice_details_id
JOIN gen.tbl_purchase_request_details tp ON tin.invoice_details_id = tp.id
WHERE (po_date BETWEEN CAST('2019-06-01' AS DATE) AND CAST('2019-06-30' AS DATE))
AND ti.remarks LIKE CAST('' AS char)
AND po_locations LIKE CAST('%Gtsi%' AS char)
AND tp.description NOT LIKE CAST('%Servicing%' AS char)
OR po_locations LIKE CAST('%Gtsi%' AS char)
AND tp.description NOT LIKE CAST('%Servicing%' AS char)
AND (ti.remarks LIKE CAST('%For Jun%' AS char)
OR ti.remarks LIKE CAST('%Recovery%' AS char)
OR ti.remarks LIKE CAST('%Capex%' AS char)
OR ti.remarks LIKE CAST('%Charge%' AS char)) ORDER BY po_date