SQL SERVER – Puzzle – Incorrect Results with Decimal

June 28, 2017

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.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal1

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.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal2

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.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal3

Puzzle Hint

If you can’t figure out the correct answer, here is another image which can potentially guide you with the correct answer.

SQL SERVER - Puzzle - Incorrect Results with Decimal puzzledecimal4

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)

SQL Datatype, SQL Function, SQL Scripts, SQL Server

SQL SERVER – Why We have Audit Trace in DATA Folder? What are These Files?

SQL SERVER – Puzzle – Write a Shortest Code to Produce Zero

245 Comments. Leave new

Sumankar Mitra
June 29, 2017 9:54 am
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
Reply
Paul Berndsen
June 29, 2017 10:02 am
That was an easy one:
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
Reply
Peter Lee
June 29, 2017 10:10 am
SELECT ID, CAST((Price*Discount)*1.0/100.0 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount)*1.0/100.0 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Reply
Rajashekar G
June 29, 2017 10:14 am
SELECT ID, CAST((Price*Discount)/cast (100 as decimal (10,2)) AS DECIMAL(10,2)) TotalDiscount,
Price – CAST((Price*Discount)/cast (100 as decimal (10,2)) AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
Reply
Bhaskar Darla
June 29, 2017 10:20 am
Hi there, with below query i got exactly results
— SELECT statement (Incorrect result)
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
Reply
Lokesh Vinnakota
June 29, 2017 10:25 am
Hi all,
As PRICE and DISCOUNT both are integers and a division between two integers(Price*Discount)/100) would only result in an integer, which is discarding the decimal part.
So for this what we have done is like, we made the division between a Integer(Price*Discount) and a Decimal(100.0) which will ultimately give us a result with 6 decimal places.
Now with our CAST, we are just trimming the result to 2 decimal Places. This is our solution.
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
Note: In the article, I find two different logics for FINALPRICE
1. Price * CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
2. Price – CAST((Price*Discount)/100.0 AS DECIMAL(10,2)) FinalPrice
I believe the 2nd gives us the desired result.
Reply
Satheesh
June 29, 2017 10:29 am
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
Reply
Ahamed Shakir
June 29, 2017 10:31 am
SELECT ID, CAST((Price*Discount)*0.01 AS DECIMAL(10,2)) TotalDiscount,
(Price-CAST((Price*Discount)*0.01 AS DECIMAL(10,2))) FinalPrice
FROM #TestTable
GO
this is giving correct answer
Reply
Ulvi
June 29, 2017 10:32 am
— SELECT statement (Incorrect result)
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
Reply
adithya
June 29, 2017 10:49 am
one of the way to get o/p is
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
SELECT ID
,CAST((Price*Discount) /100.0 AS DECIMAL(10, 2)) TotalDiscount
,CAST(Price AS DECIMAL(10, 2)) – CAST((Price*Discount) /100.0 AS DECIMAL(10, 2)) FinalPrice
FROM #TestTable
GO
Reply
gupibaghablogyotirmaya Sikder
June 29, 2017 10:49 am
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
Reply
Prabhakar
June 29, 2017 11:05 am
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
Reply
Rohit
June 29, 2017 11:10 am
Corretct query:
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
Reply
Rina Prajapati
June 29, 2017 11:17 am
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
Reply
Karthik
June 29, 2017 11:20 am
SELECT
ID,
cast(CAST(Price AS DECIMAL(10, 2)) * discount * 0.01 as decimal(10,2)) AS Totaldiscount,
cast(CAST(Price AS DECIMAL(10, 2)) – (CAST(Price AS DECIMAL(10, 2)) * discount * 0.01) as decimal(10,2)) FinalPrice
FROM #TestTable WITH (NOLOCK)
Reply
Hans Dahners
June 29, 2017 11:22 am
The integer variables need to be casted as decimal/floating point before division, as integer data type cannot support decimal positions.
Reply
vishal
June 29, 2017 11:38 am
We can do it as below:
— SELECT statement (Incorrect result)
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
SELECT ID, CAST((cast(Price AS DECIMAL(10,2))*CAST(Discount AS DECIMAL(10,2))/100) AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((cast(Price as decimal(10,2))*CAST(Discount AS DECIMAL(10,2))/100) 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
but can you tell me the difference .
Reply
vijendra singh
June 29, 2017 11:39 am
SELECT ID, CAST((cast(Price as decimal(10,2))*cast(Discount as decimal(10,2)))/100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((cast(Price as decimal(10,2))*cast(Discount as decimal(10,2)))/100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
Reply
Radhakrishna Alaparthi
June 29, 2017 11:40 am
select id,CAST(CAST(discount AS DECIMAL) * CAST(price AS DECIMAL) /100 AS DECIMAL(10,2)) TOTALDISCOUNT,
price-CAST(CAST(discount AS DECIMAL) * CAST(price AS DECIMAL) /100 AS DECIMAL(10,2)) FINALPRICE from #t1
Reply
Abhishek Bhalani
June 29, 2017 11:48 am
IF OBJECT_ID(‘tempdb..#TestTable’) IS NOT NULL
BEGIN
DROP table #TestTable
END
— 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
— SELECT statement (Incorrect result)
SELECT ID, CAST((Price*Discount) AS DECIMAL(10,2)) /cast(100 AS DECIMAL(10,2)) TotalDiscount,
Price*CAST((Price*Discount) AS DECIMAL(10,2))/ cast(100 AS DECIMAL(10,2)) FinalPrice
FROM #TestTable
GO
Reply