Sql query result not same with the total

Question

1.00/5 (1 vote)

See more:

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:

SQL

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

Posted 17-Jul-19 21:02pm

Member 14076249

Updated 17-Jul-19 21:51pm

v2

Add a Solution

Comments

F-ES Sitecore 18-Jul-19 4:15am

I'd recommend working on a much smaller result set of 10 to 100 records that also create a mismatch and try and work out what is happening from that. It's almost impossible to tell what might be wrong from a huge result set.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2019-07-17T21:51:00

Quote:
I tried using EXPLAIN and this is my result

The obvious answer is that some records that are in excel are getting filtered in the query.
The only way to go is to get the list of all records from the query and compare them with excel list to see what is missing or different.
When you know which record is filtered, check the query to see the reason.
Bad news, we can't do it for you.