tags:

views:

195

answers:

4

I have a Total value that I need to distribute among several rows in a SQL table:

DECLARE @total numeric(38,5);
DECLARE @count int;

SET @total=123.10000

SET @count = SELECT COUNT(*) FROM mytable WHERE condition=@val;
-- let's say @count is now 3

UPDATE mytable SET my_part=@total/@count WHERE condition=@val;

--each record now has 41.03333

SELECT SUM(my_part) FROM mytable where condition = @val;

-- the sum is 123.09999, not my original 123.10000

Obviously, the original total wasn't evenly divisible by 3 so the SUM won't match the original value. And no matter what I use for scale, there will be possible divisions like this one that can't line back up.

What I would like is that one of the UPDATEd rows would contain 41.03334, and the other two would have 41.03333. I don't care which ones round up and which round down. But I care that the values can be re-summed to get the original total. Is this possible? Are there known algorithms for doing this kind of thing?

+3  A: 

Put the remainder into a secret account that slowly accumulates fractional pennies... then wait a few years...

Actually, if you have SQL Server 2005+, you can use the TOP 1 clause in the UPDATE to limit the updated rows. So maybe:

DECLARE @EPSILON numeric(38,5);
DECLARE @T1 numeric(38,5);
DECLARE @T2 numeric(38,5);
SET @T1 = 1;
SET @T2 = 3;
SET @T1 = @T1/@T2;
SET @T2 = 3 * @T1;
SET @EPSILON = 1 - @T2;


DECLARE @total numeric(38,5);
DECLARE @count int;

DECLARE @REMAINDER numeric(38,5);
DECLARE @PARTIAL numeric(38,5);
DECLARE @RESUM numeric(38,5);
DECLARE @LIMITN Integer;

SET @total=123.10000;

SELECT @count = COUNT(*) FROM mytable WHERE condition=@val;

SET @PARTIAL = @TOTAL / @COUNT;
SET @RESUM = @PARTIAL * @COUNT;
SET @REMAINDER = @TOTAL - @RESUM;
IF @REMAINDER < 0 SET @EPSILON = -@EPSILON;
SET @LIMITN = @REMAINDER / @EPSILON; 

UPDATE mytable SET my_part=@PARTIAL WHERE condition=@val;

UPDATE TOP @LIMITN mytable SET my_part = my_part + @EPSILON WHERE condition=@val;

SELECT SUM(my_part) FROM mytable where condition = @val;
Joe Koberg
Like in Superman III
Joe Koberg
This only works for SQL Server 2005+ because that's when "TOP ..." seems to be included in UPDATE syntax. Otherwise, if you had any other unique Row ID available, you could sort on that and only update the minimum or maximum row to re-add the remainder.
Joe Koberg
I don't like this because if the rounding error is .00002 instead of .00001 I'd rather see it distributed to two random rows rather than all on a single row. This definitely matters once we're distributing across 10,000s of rows.
Clyde
Code edited to handle this generally. Tricky and hackish.
Joe Koberg
+1  A: 

You could use fractions to avoid rounding problems. At least multiplication and division of several rows would be easy. SUM() would not be quite so easy, if you need the exact value.

Kaniu
I presume you are talking about storing a numerator and denominator. I am actually a big fan of precise rationals!
Joe Koberg
Now the client has decided that the sums don't need to match so it doesn't matter, but this seems like a sensible way of keeping exact values if I need to in the future.
Clyde
Except you will have to write all your own arithmetic functions to do virtually anything with the numerator and denominator columns.
Joe Koberg
A: 

Below is a hack, but it works for this situation:

DECLARE @total numeric(38,5);
DECLARE @count int;

declare @mytable table
(
   my_part numeric(38,6)  --note the scale is +1
)

insert into @mytable values (0)
insert into @mytable values (0)
insert into @mytable values (0)

SET @total=123.10000

SELECT @count = COUNT(*) FROM @mytable 
-- let's say @count is now 3

UPDATE @mytable SET my_part=@total/@count;

--each record now has 41.03333

-- note the cast
SELECT cast(SUM(my_part) as numeric(38,5)) FROM @mytable;
Austin Salonen
this is probably as close as it could be, but note that it only works because @count is less than 10. If count was >= 10, you'd need 2 extra scale in the detail table as compared to total. If count is >= 100, you'd need 3 extra scale, and if count is unknown at code-writing time, then unknown extra scale is needed.
Clyde
I think you could push it out to the max and still be ok as long as the datatype on @total and cast at the end are the same.
Austin Salonen
A: 

try this

DECLARE @total numeric(38,5);
DECLARE @count int;
SET @total=123.10000
SELECT @count = COUNT(*) 
FROM mytable 
WHERE condition=@val;
-- let's say @count is now 3
UPDATE mytable SET 
    my_part = @total/@count
WHERE condition=@val;
Update mytable SET 
    my_part = my_part +  
        @total - (Select Sum(My_Part)
                  From mytable 
                  Where condition=@val)
Where PK = (Select Max(PK) From mytable 
            Where condition=@val)
-- each record except one with highest PK now has 41.03333
-- the one with highest PK has 41.03334 (or whatever)
SELECT SUM(my_part) 
FROM mytable 
where condition = @val;
-- the sum should be the original 123.10000
Charles Bretana