Help in getting my SQL statement to work

[neilfurry]

I would like to simplify my request... here is my query

SELECT
emp_id,
ROUND(SUM((Invoice.invoiceTotal-NewQuote.total)*.30),2) AS thirtypercent,
ROUND(SUM(NewQuote.total*.20),2) AS twentypercent
FROM `NewQuote`
INNER JOIN Invoice ON Invoice.quoteID=NewQuote.quoteID
INNER JOIN schedules ON schedules.quoteId=NewQuote.quoteID
WHERE (rem=1 OR rem=2)
AND (schedules.redo IS NULL)
AND invoiceTotal!=0
AND (DATE_FORMAT(date_start, '%m/%d/%Y') >= '07/01/2016')
AND ((DATE_FORMAT(date_start, '%m/%d/%Y')<='07/31/2016'))
GROUP BY emp_id

What im aiming here is this line "AND invoiceTotal!=0" should only be used in getting the thirtypercent and should not be applied in getting the twentypercent.

Can you help me on how i can do this?
Thank you

[Barand]

Why waste time converting to an unusable date format? You cannot correctly compare dates in formats other than yyyy-mm-dd. (08/01/2015 is greater than 07/01/2016)

 

Use

... AND date_start BETWEEN '2016-07-01' AND '2016-07-31'

As for your other problem, remove the "invoice_total != 0" from the where clause and use it in a case statement when calculating the 30%

[neilfurry]

hi

 

im not having a problem on the time format here.. my only problem is how to implement the "invoice_total != 0" for 30% and it will not be used for 20%

[Barand]

my only problem is how to implement the "invoice_total != 0" for 30% and it will not be used for 20%

Then read the reply - I have told you how.

 

im not having a problem on the time format here..

Yes you are, you just don't realize it.