## The Missing Price

 Author Message r0yster Mr or Mrs. 500 Group: General Forum Members Points: 576 Visits: 140 I have to go with the 'calculation is wrong' group. While the integer vs decimal part is also a problem, the first and foremost problem is the calculation is dead wrong and would never provide a correct result. Now if part of the intention was to have an incorrect calculation as a distraction from the integer vs decimal piece of the puzzle then bravo, point taken. However a simpler question to make the same point would be more likewhy does select 5/10 not = .5?Just saying.... Sean Lange SSC Guru Group: General Forum Members Points: 147415 Visits: 18564 Holy cow people. Pretty nasty responses to the QOTD. In fact the ONLY possible right answer to the question is the one about datatypes. Yes the calculation is incorrect but that is not relevant to the question being asked here. It states that the query provided a certain set of results, it did NOT state that the results were incorrect. The question was why are the results what they are. Perhaps a correct calculation but still having the integer division issue would have been better but given the verbiage of the question there is only one possible correct answer. The fact that the calculation is incorrect is something entirely different. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) Jacob Wilkins SSCoach Group: General Forum Members Points: 15563 Visits: 11812 Heh, well, to be fair, the question is incredibly vague. It's "Why do I get the result I get?"The are many factors that combine to get this particular result. Yes, it is true that the behavior of integer division contributes to this result (i.e., the result would be different if DiscountPercentage were not an integer and DiscountPercentage/100 returned something non-zero, or if the literal were 100.0).However, the same is true of the calculation being incorrect. If the calculation were correct, the results would also be different. You need both of the integer division behavior and this particular incorrect calculation to get these results.Each is only a partial explanation of why we get the particular results we get.If the question had been "Why do I get these values for FinalCost, instead of 50.00, 199.95, and 1099.90 (my expected results)?" Then absolutely, the current answer would be the only correct one.As it stands, though it's a poor question/answer set. Cheers! Bobby Russell SSCommitted Group: General Forum Members Points: 1696 Visits: 541 The calculation is wrong is ALSO the correct answer. Sean Lange SSC Guru Group: General Forum Members Points: 147415 Visits: 18564 +xBobby Russell - Monday, July 17, 2017 8:11 AMThe calculation is wrong is ALSO the correct answer.The calculation is not going to produce the desired output but the question clearly asks why are they getting the result they are getting. It does not say what is wrong with the query. _______________________________________________________________Need help? Help us help you. Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.Need to split a string? Try Jeff Modens splitter.Cross Tabs and Pivots, Part 1 – Converting Rows to Columns Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs Understanding and Using APPLY (Part 1)Understanding and Using APPLY (Part 2) Steve Holle SSC Veteran Group: General Forum Members Points: 268 Visits: 140 +xJacob Wilkins - Monday, July 17, 2017 7:36 AMHeh, well, to be fair, the question is incredibly vague. It's "Why do I get the result I get?"The are many factors that combine to get this particular result. Yes, it is true that the behavior of integer division contributes to this result (i.e., the result would be different if DiscountPercentage were not an integer and DiscountPercentage/100 returned something non-zero, or if the literal were 100.0).However, the same is true of the calculation being incorrect. If the calculation were correct, the results would also be different. You need both of the integer division behavior and this particular incorrect calculation to get these results.Each is only a partial explanation of why we get the particular results we get.If the question had been "Why do I get these values for FinalCost, instead of 50.00, 199.95, and 1099.90 (my expected results)?" Then absolutely, the current answer would be the only correct one.As it stands, though it's a poor question/answer set. Cheers!Absolutely right. If the formula is incorrect it's ridiculous to play around with the data types. Steve Holle SSC Veteran Group: General Forum Members Points: 268 Visits: 140 The operation was a success but the patient died. Word games aside, the question was completely wrong. Bobby Russell SSCommitted Group: General Forum Members Points: 1696 Visits: 541 +xSean Lange - Monday, July 17, 2017 8:26 AM+xBobby Russell - Monday, July 17, 2017 8:11 AMThe calculation is wrong is ALSO the correct answer.The calculation is not going to produce the desired output but the question clearly asks why are they getting the result they are getting. It does not say what is wrong with the query.Never said it didn't....said there are TWO correct answers. He never stated WHAT was wrong with his answers, only that they were wrong. n.ryan Ten Centuries Group: General Forum Members Points: 1072 Visits: 326 Part of the question is:I am trying to write a query that will give me the final price.Responses that the calculation are incorrect, and even good ways to perform the calculation efficiently, are acceptable here.The next section:However, I get this result:Does not ask whether the values are wrong because of the calculation or the assumed use of integer values in the column definitions. We can only make educated guesses at the use of integer values in the column definitions (most likely are, but not necessarily) however we can categorically state that the calculation is incorrect. n.ryan Ten Centuries Group: General Forum Members Points: 1072 Visits: 326 For those that care about the assumption of integer compared to a floating point column definition, try this:`DECLARE @LineItem TABLE ( LineItemKey INTEGER NOT NULL PRIMARY KEY, Qty INTEGER, Price INTEGER, DiscountPercentage INTEGER);INSERT INTO @LineItemVALUES(1,10,5,0),(2,25,8,5),(3,100,11,10);SELECT LineItemKey, Qty, Price, DiscountPercentage, FinalCost = CAST((Qty * Price) - (DiscountPercentage / 100) AS DECIMAL(10, 2))FROM @LineItem; `This gives the columnar output (sorry about the formatting):`LineItemKey Qty Price DiscountPercentage FinalCost----------- ----------- ----------- ------------------ ---------------------------------------1 10 5 0 50.002 25 8 5 200.003 100 11 10 1100.00`Change the column definition above for the Price and DiscountPercentage fields to FLOAT, gives this (again, sorry about the formatting):`LineItemKey Qty Price DiscountPercentage FinalCost----------- ---------------------- ---------------------- ---------------------- ---------------------------------------1 10 5 0 50.002 25 8 5 199.953 100 11 10 1099.90`Discerning the column type is not as straight forward as the output column format, it's a case of deducing that the DiscountPercentage field is an INTEGER type and not a float - this is the only one that matters in this broken formula and the only possibilty for producing an inner calculation result of 0.Quite what lunatic would specify price as a FLOAT or an INTEGER type is a different question altogether...