Can this be done without a loop or cursor

Question

I have a table that list individual items and the amount we billed for them. We receive a payment that may be less than the total amount billed. I want to allocate that payment to each item in proportion to the original billed amount.

Here's the tricky part.

• Each individual paid amount can not have fractional cents

• The sum of the individual paid amounts must still add up to the TotalPaid Amount.

Setting up the data:

declare  @t table
(
   id varchar(4) primary key, 
   Billed money not null
)


insert into @t
(
id,
billed
)
values
( 'A', 5),
( 'B', 3),
( 'C', 2)

 declare @TotalPaid money
 Set @TotalPaid = 3.33

This way doesn't work

 SELECT
      ID, 
      Round(@TotalPaid * Billed / (Select Sum(Billed) from @t), 2)
 From
      @T

it will return:

 A  1.67
 C  1
 D  0.67
    -----
    3.34 <--- Note the sum doesn't equal the Total Paid

I know I can accomplish this via a cursor or a loop, keeping track of the unallocated amount at each step and insuring that after the last item the entire TotalPaid amount is allocated.

However I was hoping there was a way to do this without a loop or cursors.

This is a greatly simplified version of the problem I'm trying to address. The actual data has over 100K rows and the cursor approach is really slow.

Answer 1

I think this is a viable approach...

(Pass 1 as the third parameter to ROUND to ensure rounding is always down then distribute the odd 0.01s that make up the balance to ones where the difference between the rounded amount and the ideal amount is the greatest)

WITH t1
     AS (SELECT *,
                billed_adj = @TotalPaid * Billed / Sum(Billed) OVER(),
                billed_adj_trunc = ROUND(@TotalPaid * Billed / Sum(Billed) OVER(), 2, 1)
         FROM   @t)
SELECT id,
       billed,
       billed_adj_trunc + CASE
                            WHEN ROW_NUMBER() OVER (ORDER BY billed_adj - billed_adj_trunc DESC) 
                                           <= 100 * ( @TotalPaid - SUM(billed_adj_trunc) OVER() )
                              THEN 0.01
                            ELSE 0
                          END
FROM   t1
ORDER  BY id 

Answer 2

Here is a (somewhat complicated) solution using a recursive common table expression

;with cte as (
  select
      id
    , Paid = round(@TotalPaid * Billed / (Select Sum(Billed) from @t), 2,1)
    , Remainder =  @TotalPaid * Billed / (Select Sum(Billed) from @t)
           - round(@TotalPaid * Billed / (Select Sum(Billed) from @t), 2,1)
    , x.next_id
  from @t t
    outer apply (
      select top 1 next_id = i.id
      from @t as i
      where i.id > t.id
      order by i.id asc
    ) x
)
, r_cte as (
    --anchor row(s) / starting row(s)
  select 
      id
    , Paid
    , Remainder
    , next_id
  from cte t
  where not exists (
    select 1
    from cte as i
    where i.id < t.id
    )
  union all
  --recursion starts here
  select 
      c.id
    , c.Paid + round(c.Remainder + p.Remainder,2,1)
    , Remainder = c.Remainder + p.Remainder - round(c.Remainder + p.Remainder,2,1)
    , c.next_id
  from cte c
    inner join r_cte p
      on c.id = p.next_id
)
select id, paid 
from r_cte

rextester demo: http://rextester.com/MKLDX88496

returns:

+----+------+
| id | paid |
+----+------+
| A  | 1.66 |
| B  | 1.00 |
| C  | 0.67 |
+----+------+

Answer 3

For something like this you are not going to able to apply an exact distribution; as you hvae already shown the rounding results in the total exceeding the payment received.

You will therefore need to distribute "whatever is left" to the final [Billed], so you'll need to do 2 things...

1. Determine if the current row is the final row in that group.
2. Determine how much of the payment has already been distributed.

You don't give much data to work with here, so the following is not ideal, however this is along the lines of what you want...

SELECT
    ID, 
    CASE WHEN lead(billed,1) OVER(ORDER BY (SELECT 1)) IS NULL THEN @TotalPaid - (sum(round(@TotalPaid * Billed / (Select Sum(Billed) from @t),2)) OVER(ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING))
        ELSE round(@TotalPaid * Billed / (Select Sum(Billed) from @t),2)
    END AS solution
FROM
    @T;

Note that if the A,B,C then has a higher key this would make up the "group" so you would adjust the window functions accordingly. If you could supply some more sample data with additional columns etc. I could maybe come up with a more elegant solution.