Prorate Rounding differences - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Prorate Rounding differences

Author

Topic

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-20 : 14:16:13

I have a procedure that Prorates a dollar value to 3 Products depending on the Product Qty. What's the best way to deal with rounding differences. The QDols should match the TotDols value.

SELECT
   PROD.ProdPeriod,
   PROD.ProductID,
   QDols = ROUND(PROD.Qty / TOTL.QTotQty * QTotDols, 2)
FROM tblProducts PROD
LEFT OUTER JOIN (
   SELECT
      ProdPeriod, 
      QTotDols = Sum(CASE
         WHEN ProductID = 'TEST' THEN Dollars
         ELSE 0 END),
      QTotQty = Sum(CASE 
         WHEN ProductID IN ('Test1','Test2','Test3') THEN Qty    
         ELSE 0 END)  
   FROM tblProducts  
   WHERE ProductID IN ('TEST','Test1','Test2','Test3')
   GROUP BY ProdPeriod
) TOTL ON PROD.ProdPeriod = TOTL.ProdPeriod
WHERE PROD.ProductID IN ('Test1','Test2','Test3')

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-20 : 16:32:22

generally speakig, maintain a higher degree of numeric precision through out the calculation process then compare results rounded to a less precise number of places.

post the table DDL along with some sample rows that are giving you problems if you want to see an example.

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-21 : 11:14:58

Here are some sample rows:

Product     Qty      Dollars
TEST                 $500.00
Test1       1        
Test2       1
Test3       1

Test1, Test2, Test3 formula = 1/3 * $500 = 166.67
If I add up the 3 values it gives me $500.01, therefore I need to apply the $.01 difference to 1 of the 3 values in order for the split to balance. Qty and Dollars are both defined as Decimal(18, 5) in the tblProducts table. Currently I am doing this by first creating a temp file and then comparing the newly calculated summed up values to the QTotDols value.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-21 : 13:01:07

It looks like you have a design flaw. You are storing the total Dollars as a seperate row along with the detail items. The detail items don't have their own price. What should happen if the total is 4 cents (.04) and there are 3 products?

You should store the details and derive the total instead of the other way around.

Be One with the Optimizer
TG

ingineu
Yak Posting Veteran

89 Posts

Posted - 2006-03-22 : 03:25:23

I suppose I simplified the code beyond understanding. I am dealing with Chemicals. You assign Dollars to 1 chemical. At monthend, some chemicals are split into 2 or 3 items. That's where the prorating comes into play.

eg. CAUSTIC dollars $6000
split
CAUSTIC Cooking ... Usage Qty given
CAUSTIC Bleaching ... Usage Qty given

On the mainframe system, it was handled by prorating the difference to the larger Qty.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-22 : 08:25:19

[code]declare @amt decimal(10,2),
@adj decimal(10,2)

declare @test table
(
id int identity(1,1),
Product varchar(10),
Qty int,
Value decimal(10,2)
)

select @amt = 100

insert into @test (Product, Qty)
select 'Test 1', 1 union all
select 'Test 2', 8 union all
select 'Test 3', 8 union all
select 'Test 4', 4

update @test
set Value = Qty * @amt / (select sum(Qty) from @test)

select *, (select sum(Value) from @test) from @test

select @adj = @amt - sum(Value) from @test

update @test
set Value = Value + @adj
from @test t
where id = (select top 1 id from @test order by Qty desc, id)

select *, (select sum(Value) from @test) from @test[/code]

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-22 : 13:44:43

Don't know if you're still working on this or not. Assuming you want as even a distribution as possible, here is a solution that would split the total dollars so that all the individual items were as close as possible to each other rather than having a single "adjustment" value that could be different by like 15 cents or so from the rest of the items. This is pretty ugly and can probably be simplified but it is set-based. Basically it rounds up or down each item to end up with an even distribution of item dollars.

I think this answer is along the lines of MVJ's specialty

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=63512


set nocount on
create table #product (prodid int, totalDol money) 
create table #productSplit (prodid int, item varchar(1)) 

insert #product values (1,500) 
insert #productSplit values (1, 'a') 
insert #productSplit values (1, 'b') 
insert #productSplit values (1, 'c') 

insert #product values (2, .04) 
insert #productSplit values (2, 'a') 
insert #productSplit values (2, 'b') 
insert #productSplit values (2, 'c') 

insert #product values(3, 6000) 
insert #productSplit values (3, 'a') 
insert #productSplit values (3, 'b') 

insert #product values(4, 3000.97) 
insert #productSplit values (4, 'a') 
insert #productSplit values (4, 'b') 
insert #productSplit values (4, 'c') 
insert #productSplit values (4, 'd') 
insert #productSplit values (4, 'e') 
insert #productSplit values (4, 'f') 
insert #productSplit values (4, 'g') 
insert #productSplit values (4, 'h') 
insert #productSplit values (4, 'i') 
insert #productSplit values (4, 'j') 
insert #productSplit values (4, 'k') 
insert #productSplit values (4, 'l') 
insert #productSplit values (4, 'm') 
insert #productSplit values (4, 'n') 
insert #productSplit values (4, 'o') 
insert #productSplit values (4, 'p') 
insert #productSplit values (4, 'q') 


select	p.prodid
	,p.totalDol
	,count(*) as itemCount
from	#product p
join	#productSplit ps
		on ps.prodid = p.prodid
group by p.prodid
	,p.totalDol
order by 1

--rounded up items
select	p.prodid
	,ps.item
	,itemDollars = ceiling((p.TotalDol/ru.qty)*100)/100
from	#product p
join	(--generate a sequential itemid
	select	prodid
		,item
		,itemid=(select count(*) from #productSplit where prodid = ps.prodid and item <= ps.item)
	from	#productSplit ps	
	) ps
		on ps.prodid = p.prodid
join	(--get the quantity and the count of how many items should be rounded up vs down
	select  p.prodid
		,t.qty
		,roundupCount = convert(int,(p.TotalDol - ((floor((p.TotalDol/t.qty)*100)/100)*t.qty))*100)
	from    #product p 
	join    (--get the item quantity
	        select  prodid, count(*) qty
	        from    #productSplit ps 
	        group by prodid 
	        ) t 
	                on t.prodid = p.prodid 
	) ru
		on ru.prodid = p.prodid
		and ru.roundupCount >= ps.itemid
union all

--rounded down items
select	p.prodid
	,ps.item
	,itemDollars = floor((p.TotalDol/ru.qty)*100)/100
from	#product p
join	(--generate a sequential itemid
	select	prodid
		,item
		,itemid=(select count(*) from #productSplit where prodid = ps.prodid and item <= ps.item)
	from	#productSplit ps	
	) ps
		on ps.prodid = p.prodid
join	(--get the quantity and the count of how many items should be rounded up vs down
	select  p.prodid
		,t.qty
		,roundupCount = convert(int,(p.TotalDol - ((floor((p.TotalDol/t.qty)*100)/100)*t.qty))*100)
	from    #product p 
	join    (--get the item quantity
	        select  prodid, count(*) qty
	        from    #productSplit ps 
	        group by prodid 
	        ) t 
	                on t.prodid = p.prodid 
	) ru
		on ru.prodid = p.prodid
		and ru.roundupCount < ps.itemid

order by 1,2

drop table #product 
drop table #productsplit 

--==============================================================
--output

prodid      totalDol              itemCount   
----------- --------------------- ----------- 
1           500.0000              3
2           .0400                 3
3           6000.0000             2
4           3000.9700             17

prodid      item itemDollars           
----------- ---- --------------------- 
1           a    166.6700
1           b    166.6700
1           c    166.6600
2           a    .0200
2           b    .0100
2           c    .0100
3           a    3000.0000
3           b    3000.0000
4           a    176.5300
4           b    176.5300
4           c    176.5300
4           d    176.5300
4           e    176.5300
4           f    176.5300
4           g    176.5300
4           h    176.5300
4           i    176.5300
4           j    176.5300
4           k    176.5300
4           l    176.5300
4           m    176.5300
4           n    176.5200
4           o    176.5200
4           p    176.5200
4           q    176.5200

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources