find diference between two fields

  • ok questions. in the TSQLFundamentals2008 database production.products table has a column named unitprice (which is the price the company pays for the product?) and then in the Sales.OrderDetails a column named unitprice(which is the price they charge their customers?)

    I want to know how to query and find the different between Production.Products.unitprice - Sales.OrderDetails.unitprice

  • ok let me rephrase I know how to do the query. the problem is I can't figure out how to get the negative numbers.If there is no profit it just gives me 0. I want to know what I am losing.

    SELECT od.productid, od.orderid, pp.unitprice - od.unitprice AS Profit FROM

    Production.Products AS pp

    JOIN

    Sales.OrderDetails AS od

    ON pp.productid= od.productid

    ORDER BY od.orderid

  • SELECT od.productid, od.orderid, so.custid, so.empid,od.discount, od.unitprice - pp.unitprice AS Profit FROM

    Production.Products AS pp

    JOIN

    Sales.OrderDetails AS od

    ON pp.productid = od.productid

    JOIN

    Sales.Orders AS so

    ON od.orderid = so.orderid

    ORDER BY od.orderid

  • mrwillcostello (1/14/2012)


    ok let me rephrase I know how to do the query. the problem is I can't figure out how to get the negative numbers.If there is no profit it just gives me 0. I want to know what I am losing.

    SELECT od.productid, od.orderid, pp.unitprice - od.unitprice AS Profit FROM

    Production.Products AS pp

    JOIN

    Sales.OrderDetails AS od

    ON pp.productid= od.productid

    ORDER BY od.orderid

    What does this query return?

    SELECT od.productid, od.orderid, pp.unitprice - od.unitprice AS Profit FROM

    Production.Products AS pp

    JOIN

    Sales.OrderDetails AS od

    ON pp.productid= od.productid

    WHERE pp.unitprice > od.unitprice

  • SELECT

    od.productid,

    od.orderid,

    so.custid,

    so.empid,

    od.discount,

    od.unitprice, -- what do you see here?

    pp.unitprice, -- what do you see here?

    od.unitprice - pp.unitprice AS Profit -- does the calculation make sense now?

    FROM Production.Products AS pp

    JOIN Sales.OrderDetails AS od

    ON pp.productid = od.productid

    JOIN Sales.Orders AS so

    ON od.orderid = so.orderid

    ORDER BY od.orderid


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • HINT: Look at the data types for the columns you are doing the difference of. Are you sure that the difference can be negative based on those data types? 🙂

    Jared
    CE - Microsoft

  • SQLKnowItAll (1/16/2012)


    HINT: Look at the data types for the columns you are doing the difference of. Are you sure that the difference can be negative based on those data types? 🙂

    Aren't they of type MONEY? Can't the difference be negative? Just wondering!

  • patrickmcginnis59 (1/16/2012)


    SQLKnowItAll (1/16/2012)


    HINT: Look at the data types for the columns you are doing the difference of. Are you sure that the difference can be negative based on those data types? 🙂

    Aren't they of type MONEY? Can't the difference be negative? Just wondering!

    I don't know what the types are, you would have to tell me. However, with the results you are getting I cannot come up with another explanation. Clearly 3 - 2 <> 0, so something has to be going on there. That is where I would look first, then expand further if it is not the answer.

    Jared
    CE - Microsoft

  • SQLKnowItAll (1/16/2012)


    patrickmcginnis59 (1/16/2012)


    SQLKnowItAll (1/16/2012)


    HINT: Look at the data types for the columns you are doing the difference of. Are you sure that the difference can be negative based on those data types? 🙂

    Aren't they of type MONEY? Can't the difference be negative? Just wondering!

    I don't know what the types are, you would have to tell me. However, with the results you are getting I cannot come up with another explanation. Clearly 3 - 2 <> 0, so something has to be going on there. That is where I would look first, then expand further if it is not the answer.

    I'm guessing based on the included name that he's running queries on the database downloadable here: http://www.sql.co.il/books/tsqlfund2008/, and the two columns are of type MONEY.

  • Sorry everyone. The last query that I posted was the results that I wanted.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply