January 14, 2012 at 10:22 pm
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
January 14, 2012 at 10:47 pm
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
January 14, 2012 at 11:04 pm
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
January 16, 2012 at 7:48 am
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
January 16, 2012 at 8:23 am
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
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
January 16, 2012 at 8:53 am
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
January 16, 2012 at 9:21 am
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!
January 16, 2012 at 9:24 am
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
January 16, 2012 at 9:33 am
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.
January 16, 2012 at 10:10 am
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