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
I found other ways to get the same result but the ” / 100″ is confusing me. Curious to see what the code is you have in place.
SELECT ID, CAST((Price*Discount)*100 /100
AS DECIMAL(10,2)) TotalDiscount,
Price *CAST((Price*Discount) *100/100
AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
I GOT ONLY THIS RESULT. I Am Failed To Solve This Sorry Sir
ID TotalDiscount FinalPrice
1 5.00 5.00
2 10.00 20.00
3 15.00 45.00
4 20.00 80.00
Missing portion is 1.0
SELECT ID, CAST((Price*Discount)/97.8 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/97.8 AS DECIMAL(10,2)) FinalPrice
FROM bank.dbo.TestTable
GO
SELECT ID, CAST((Price*Discount)/97.8 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/97.8 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
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 * from #TestTable
SELECT ID, CAST((Price*Discount)*0.01*100/100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)*0.01*100/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
SELECT ID, CAST((Price*Discount*1.00)/100 AS DECIMAL(10,2)) TotalDiscount,(Price*Discount),
Price*CAST((Price*Discount*1.00)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
There are at least two ways this statement will work.
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
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
*1.00
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
We can modify the query as follows, and it will return the required results.
SELECT ID, cast(((Price*Discount)*.01) AS decimal(10,2)) TotalDiscount,
Price-CAST(((Price*Discount)*.01) AS decimal(10,2)) FinalPrice
FROM #TestTable
GO
The problem is the implicit conversion to integer because or your divisor (100)… If you change it to 100.0 it will work:
SELECT ID, Price, CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
I prefer using the money object type, but the conversion would be the same:
SELECT ID
, CAST(Price * Discount AS MONEY) / 100 AS ‘TotalDiscount’
, CAST(Price AS MONEY) – CAST(price * Discount AS MONEY) / 100 AS ‘FinalPrice’
FROM #TestTable
SELECT CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) TotalDiscount,
Price-CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
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
First, you really threw me with the symbol change for the FinalPrice field. You example code showed Price*CAST… then your hint code shows Price-CAST… Once I got that, then my nested CAST worked for me.
SELECT ID, 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
Question you asked:
SELECT ID, CAST((Price*Discount)/100 AS DECIMAL(10,2)) TotalDiscount, Price*CAST((Price*Discount)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
This is the question for the answer you mentioned above :
“SELECT ID, CAST(CAST((Price*Discount)as float)/100 AS DECIMAL(10,2)) TotalDiscount,
CAST(Price-CAST((Price*Discount)AS FLOAT)/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable”
The symbol is different in above mentioned question and the hint you have given,
First query we have (*) symbol.
In second one we have (-) symbol.
Dividing by 100 which is an integer will result in integer only. But to the output we are casting it to decimal, the result is displayed in decimal format.
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