SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Missing Price


The Missing Price

Author
Message
r0yster
r0yster
SSC Veteran
SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)SSC Veteran (260 reputation)

Group: General Forum Members
Points: 260 Visits: 111
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 like

why does select 5/10 not = .5?

Just saying....
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63314 Visits: 17965
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
Jacob Wilkins
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7587 Visits: 10183
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
Bobby Russell
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 511
The calculation is wrong is ALSO the correct answer.
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63314 Visits: 17965
Bobby Russell - Monday, July 17, 2017 8:11 AM
The 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
Steve Holle
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 140
Jacob Wilkins - Monday, July 17, 2017 7:36 AM
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!


Absolutely right. If the formula is incorrect it's ridiculous to play around with the data types.
Steve Holle
Steve Holle
SSC-Enthusiastic
SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)SSC-Enthusiastic (118 reputation)

Group: General Forum Members
Points: 118 Visits: 140
The operation was a success but the patient died. Word games aside, the question was completely wrong.

Bobby Russell
Bobby Russell
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 511
Sean Lange - Monday, July 17, 2017 8:26 AM
Bobby Russell - Monday, July 17, 2017 8:11 AM
The 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
n.ryan
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 255
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
n.ryan
SSC-Addicted
SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)SSC-Addicted (457 reputation)

Group: General Forum Members
Points: 457 Visits: 255
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... Smile
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search