Rounding with SQL Server

Factual Questions
1

I’ve been banging my head about a rounding issue in SQL Server for a little while now with no luck so thought I’d turn to you guys to see if any of you have suggestions.

We have a product whose price includes fractions of a cent. For example, 78.35 cents per pound. When we invoice our clients we simply charge them the total weight of the product they buy * the price, round to 2 decimals, and store that value in our database.

Now I’m trying to generate some reports which we’ll be using in the office that breaks some of those shipments into lots. When I calculate sub totals for each lot and display as a price to the consumer, I end up getting a rounding error in the end. The totals of the invoiced amounts for each lot don’t add up to the actual invoice amount.

If I was just using this report in a program I could easily modify the last invoice value and put the rounding error in there so that everything would total in the end. But these numbers will be used in multiple reports both now and in the future so I’d much rather the SQL view take care of the problem.

Is there any way, in a view in SQL, that I can apply some programming logic to handle the rounding issue on the detailed breakdown?

Thanks for any help!

2

Let me see if I understand you problem correctly.

Let’s say, for a particular lot, the total price would be $100.

That lot was broken into three sales, of the following values

Sale 1: Actual cost: $25.454 - stored as $25.45
Sale 2: Actual cost: $35.234 - stored as $35.23
Sale 3: Actual cost: $39.312 - stored as $39.31

So, the actual total adds to $100, but the stored totals add to $99.99?

That’s not a SQL problem, that’s a business logic problem.

EDIT: what will be more problematic is when the rounding goes the other way and someone wants to know why you charge $100.01 for something that costs $100.

3

That’s the problem, but it’s 1 sale broken into multiple shipments, or in this case, containers. We’re trying to show the invoiced value per container on a ship. If the customer adds up what we say on the container he may get a different value than what he was charged based purely because his price was rounded once vs a cumulative rounding error over 15 or 20 containers.

4

Store the data in a currency field. It will automatically round the cost properly.

Your rounding to 2 decimals is most likely the formatting of the value and not what is actually stored in the table.

5

JerrySTL, I think what the OP is saying is the customer was charged $100.00, and that’s what’s stored in the database. Now he has a report that breaks this into 3 lots. To take a simple case of equal amounts in each, the report shows $33.33 for each lot, summing to $99.99. The total amount ($100.00) is what is stored, so changing a datatype or rounding in the database won’t help.

Frazzled, if I understand correctly, I think you want a view that picks one lot to apply the difference to, right? So in the example above, the report would calculate one lot as ($100.00 - 33.33 - 33.33) = $33.34?

I don’t think you can do that with a single view. Maybe you could create a view that calculates the difference of the summed lots and the invoiced totals, then your report could add that to one lot.

Or you could take the most common approach and add a note at the bottom of the report, “due to rounding, the sum of all lots might not equal the invoiced amount.”

6

I’ve had this come up with catch weight customers and in cases of prorated shipment costs multiple times.

There are a few ways to handle it depending on the situation but it sounds like you want to assign the error/difference to one lot so here’s an example solution:



Select
 OH.*
,LH.*
,LD.*
--
-- Calc Lot Amount plus error (for min lot)
,(case when LH.LotNumber<>t1.min_LotNumber 
		then LH.LotAmount
		else LH.LotAmount + (OH.Order_Amount - T1.sum_LotAmount) end) as ResolvedLotAmnount
--
-- Order Hdr, Lot Hdr and Lot Dtl you are interested in
from OrderHeader OH
inner join LotHeader  LH
  on OH.OrderNumber=LH.OrderNumber
inner join LotDetail  LD
  on LH.LotNumber=LD.LotNumber
--
-- Get total order info
join (	--
	-- Summarize by Order
	select
	 t0.OrderNumber
	,min(LotNumber) 	as min_LotNumber
	,sum(LotAmount) 	as sum_LotAmount
	from (	--
		-- Summarize by Lot
		select
		 OH2.OrderNumber
		,LH2.LotNumber
		--
		-- Add whatever rounding you do at either detail level or at summary level or both
		,sum(LD2.Qty * LD2.Price) as LotAmount
		from OrderHeader OH2
		inner join LotHeader  LH2
		  on OH2.OrderNumber=LH2.OrderNumber
		inner join LotDetail  LD2
		  on LH2.LotNumber=LD2.LotNumber 
		group by
		 OH2.OrderNumber
		,LH2.LotNumber
	) t0
	group by
	 t0.OrderNumber
) t1
  on OH.OrderNumber=t1.OrderNumber
7

That’s a beautiful solution and it worked perfectly! Thank you so much for your help!