Simpify my query

Discussion in 'Databases' started by neilfurry, Jul 18, 2016.

0
  1. #1
    hi can you help me simplifying my query:

    SELECT SUM(NewQuote.total*.20) + (SELECT SUM((Invoice.invoiceTotal-NewQuote.total)*.30)
    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')) )AS totalcom

    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 (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

    Thank you
     
    neilfurry, Jul 18, 2016 IP
  2. neilfurry

    neilfurry Active Member

    Messages:
    55
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    63
    #2
    I would like to simplify my request... here is a revision of 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
     
    neilfurry, Jul 18, 2016 IP