neilfurry Posted July 18, 2016 Share Posted July 18, 2016 I would like to simplify my request... here is my querySELECTemp_id,ROUND(SUM((Invoice.invoiceTotal-NewQuote.total)*.30),2) AS thirtypercent,ROUND(SUM(NewQuote.total*.20),2) AS twentypercentFROM `NewQuote`INNER JOIN Invoice ON Invoice.quoteID=NewQuote.quoteIDINNER JOIN schedules ON schedules.quoteId=NewQuote.quoteIDWHERE (rem=1 OR rem=2)AND (schedules.redo IS NULL)AND invoiceTotal!=0AND (DATE_FORMAT(date_start, '%m/%d/%Y') >= '07/01/2016')AND ((DATE_FORMAT(date_start, '%m/%d/%Y')<='07/31/2016'))GROUP BY emp_idWhat 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted July 18, 2016 Share Posted July 18, 2016 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% Quote Link to comment Share on other sites More sharing options...
neilfurry Posted July 19, 2016 Author Share Posted July 19, 2016 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% Quote Link to comment Share on other sites More sharing options...
Barand Posted July 19, 2016 Share Posted July 19, 2016 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.