0

In some calculations, I am expecting to receive a value out to 15 decimal places but I am only getting it out to 6 decimal places.

In the example below, For @ProRata I am getting the value 0.8871340000000 when I expect 0.887134492110339.

DECLARE @EligiblePurchase DECIMAL(38,15) = 0
   ,@ProRata DECIMAL(38,15) = 0                 --Percent of total purchase
   ,@Reduction DECIMAL(38,15) = 0
   ,@Denominator DECIMAL(38,15) = 355302907.06  --Total amount across all purchases
   ,@InitialValue DECIMAL(38,15) = 315201464.00 --Specific purchase

--Sometimes @Reduction will be a reduction % like 16.677777777777777, but in this case it's 0
SET @EligiblePurchase = @InitialValue * ((100.00 - @Reduction) / 100)
SET @ProRata = @EligiblePurchase / @Denominator
SELECT @ProRata

My google-fu is failing me. SqlServer is doing something I am not expecting.

Thanks in advance!!

Jeff.Clark
  • 599
  • 1
  • 5
  • 27
  • I tested it with float and it returns the correct value, however, for this precision you should not use float. Funny behavior. Check this thread, it's a very similar problem: https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92608 – iceblade Sep 21 '20 at 17:39
  • `My google-fu is failing me` - *sql server decimal division precision*, first result. – GSerg Sep 21 '20 at 17:41
  • If you check the intermediate results you'll find the `scale` comes up short here: `select @EligiblePurchase / @Denominator as Value, SQL_Variant_Property( @EligiblePurchase / @Denominator, 'basetype' ) as BaseType, SQL_Variant_Property( @EligiblePurchase / @Denominator, 'precision' ) as Precision, SQL_Variant_Property( @EligiblePurchase / @Denominator, 'scale' ) as Scale;`. It's enlightening to go through step by step this way and see the data types of the intermediate values. – HABO Sep 21 '20 at 18:04

0 Answers0