Jump to content
neilfurry

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

Share this post


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%

Share this post


Link to post
Share on other sites

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%

Share this post


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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

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.