July 17, 2017 at 8:27 am
Jacob 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.
July 17, 2017 at 8:30 am
July 17, 2017 at 8:35 am
Sean Lange - Monday, July 17, 2017 8:26 AMBobby 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.
July 17, 2017 at 9:58 am
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.
July 17, 2017 at 10:14 am
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 @LineItem
VALUES
(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.00
2 25 8 5 200.00
3 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.00
2 25 8 5 199.95
3 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... 🙂
July 17, 2017 at 12:06 pm
Sean Lange - Monday, July 17, 2017 7:11 AMHoly 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.
Agreed Sean, the response has been vitriolic to say the least and totally unnecessary IMHO, it seemed clear there were two questions being asked but one was inadvertent; "do you know basic math?" as a consequence of wording and code.
I still think it was a good question in intent and Steve now feels pretty put out, I have no doubt.
...
July 17, 2017 at 12:38 pm
My apologies. The formula was incorrect, which wasn't the point of the question. We rarely ask questions on business logic, but on SQL Server. However, this wasn't clear.
Question corrected and points awarded back.
July 17, 2017 at 12:44 pm
Heh, I hate to be that guy, but now the query actually returns correct results with this particular data because multiply and divide have the same precedence, so they're evaluated left-to-right (i.e., it'll do 25*8=200 first, since it's inside parentheses, then just left-to-right it'll do 200*5=1000 and then 1000/100=10, getting the correct discount amount).
If that expression is changed to this, you have the correct logic and run into the integer division problem:
FinalCost = CAST((Qty * Price) - ((Qty * Price) * (DiscountPercentage / 100)) AS DECIMAL(10, 2))
Cheers!
July 17, 2017 at 1:00 pm
Fixed. That's probably where the original issue created. Tested in SSMS and then tried to edit in the website since cut/paste doesn't always format correctly.
July 18, 2017 at 8:57 am
I guess I'm glad I got to the party late. I answered it correctly a bit ago and noticed nothing wrong -- until I came here and read through the comments.
Why, it almost seemed like it was a required life or death thing, and not optional reading through it.
July 18, 2017 at 9:36 am
Apparently I led myself down the wrong path right away as the QOTD in the SqlServerCentral.com email had:
FinalCost = CAST((Qty * Price) - (DiscountPercentage / 100) AS DECIMAL(10, 2))
subtracting the discount without the total and I didn't notice the change to:
FinalCost = CAST((Qty * Price) - ((Qty * Price) * (DiscountPercentage / 100)) AS DECIMAL(10, 2))
that changed the calculation for the discount when I followed the link to the page. Doh! I should pay more attention... :crazy:
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply