Jump to content

Help in getting my SQL statement to work


Recommended Posts

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

Link to post
Share on other sites

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%

Link to post
Share on other sites

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.

Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.