It has been a while we have seen a puzzle on this blog and I keep on receiving emails and comments that you all would love to see one more puzzle. Let us see a new puzzle “Incorrect Results with Decimal”.
So the question is very simple and I expect everyone of you to get the correct answer without running the code actually in SSMS. However, due to any reason, you can’t figure out the answer, please do not hesitate to see the hints and run the entire query in SSMS.
First thing first lets us set up a table. The table contains three columns – ID, Price and Discount. We need to find out the total discount per item and final price of the product after discount.
-- Create table CREATE TABLE #TestTable (ID INT, Price INT, Discount INT) -- Populate table INSERT INTO #TestTable (ID, Price, Discount) SELECT 1, 1, 5 UNION ALL SELECT 2, 2, 5 UNION ALL SELECT 3, 3, 5 UNION ALL SELECT 4, 4, 5 GO -- Data in table SELECT * FROM #TestTable GO
When we run above script we will get following answer.
Now our puzzle is to find out discount value and total final price of the product.
Here is the script which is written to find TotalDiscount and FinalPrice. However, it is giving us all the answers as 0 (zero). The script is INCORRECT.
-- SELECT statement (Incorrect result) SELECT ID, CAST((Price*Discount)/100 AS DECIMAL(10,2)) TotalDiscount, Price*CAST((Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice FROM #TestTable GO
Here is the incorrect result.
Puzzle Statement
Please fix the above script in such a way that it gives correct TotalDiscount and Final Price Value.
We are expecting the answer as in the following image.
Puzzle Hint
If you can’t figure out the correct answer, here is another image which can potentially guide you with the correct answer.
Let us see if you can give correct answer to this puzzle or not. Please share with your friends and test their knowledge about SQL as well.
NOTE: Please leave a correct answer in the comment section. I will publish all the comments together on coming Monday, till then all the answers will be hidden.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
245 Comments. Leave new
Below are the two queries
SELECT ID, CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
SELECT ID, CAST(cast((Price*Discount) as decimal(10,2))/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST(cast((Price*Discount) as decimal(10,2))/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
*1.0
The correct answer is:
–================
— SELECT statement (Incorrect result)
SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
/*
The data type of the Price and Discount columns are created as INT so INT*INT will return INT. so we are casting the results to decimals ((Price*Discount)*1.00)<=casting INT value to Decimal (this is the actual casting unless the divide will not work correctly)
*/
Hi Pinal, The missing element is *1.00
SELECT ID, CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
— SELECT statement (Incorrect result)
SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
SELECT ID, CAST(cast((Price*Discount) as float)/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST(cast((Price*Discount) as float)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
THE CORRECT ANSWER
SELECT ID, CAST((CAST(Price AS DECIMAL(9,2))*CAST(Discount AS DECIMAL(9,2)))/100 AS DECIMAL(9,2)) TotalDiscount,
        Price – CAST((CAST(Price AS DECIMAL(9,2))*CAST(Discount AS DECIMAL(9,2)))/100 AS DECIMAL(9,2)) FinalPrice
FROM #TestTable
SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
haha, this is a mistake I sometimes meet or do when I am sleepy… The cast/convert is done too late and the result of the division is an integer and so the decimal are removed.
Just add *1.00 to the numerator of the division and then the result will be decimal and will be casted afterwards
All I did was cast the Discount to Decimal(10, 2) for the TotalDiscount and the FinalPrice and it worked as intended. You “Missing Element” is different than what I found as a solution; so I’ll be interested to see what it is.
SELECT ID, Price,
CAST((Price * CAST(Discount AS DECIMAL(10,2))) / 100 AS DECIMAL(10,2)) TotalDiscount,
Price – CAST((Price * CAST(Discount AS DECIMAL(10,2))) / 100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
SELECT *,CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),Price * Discount) / 100),
Price – CONVERT(DECIMAL(10,2),CONVERT(DECIMAL(10,2),Price * Discount) / 100)
FROM #TestTable
SELECT ID, CAST((Price*Discount)*(1.00)/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*(1.00)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Alternate Solution
SELECT ID, CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/100.00 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
SELECT ID,
cast(((CAST(Price AS DECIMAL(10,2))) * cast(Discount AS DECIMAL(10,2))/100) as decimal(10,2)) TotalDiscount,
cast(CAST(Price AS DECIMAL(10,2)) – ((CAST(Price AS DECIMAL(10,2))) * cast(Discount AS DECIMAL(10,2))/100) as decimal(10,2)) as FinalPrice
FROM #TestTable
GO
Hi all,
this is my quick answer:
SELECT ID, CAST((Price*Discount)*1.00/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)*1.00 /100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
Method1:
SELECT ID, CAST((Price*Discount*1.0)/100 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount*1.0)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
Method2:
SELECT ID, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
SELECT ID, CAST((Price*Discount)+.00 /100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)+.00 /100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
SELECT ID, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
SELECT ID, CAST( CAST((Price*Discount) AS DECIMAL(10,2))/100 AS DECIMAL(10,2) )TotalDiscount,
CAST( Price-CAST((Price*Discount) AS DECIMAL(10,2))/100 AS DECIMAL(10,2) ) FinalPrice
FROM #TESTTABLE