distribute value to all rows while updating table

Question

I have table structure like tblCustData

ID    UserID    Fee FeePaid
 1      12      150   0
 2      12      100   0
 3      12      50    0

And value to be update in FeePaid Column such that if i have value in @Amt variable in 200 Then it should update any two rows

Output should be like

ID    UserID    Fee FeePaid
1      12       150   150
2      12       100   50
3      12       50    0

FeePaid should not be grater than Fee Column But if i pass 350 in @Amt variable it should produce output like

ID    UserID    Fee FeePaid
1       12      150   200
2       12      100   100
3       12      50    50

Only if @Amt is exceeding the total value in Fee column

I can not think beyond this query

Update tblCustData
Set FeePaid=@Amt
Where UserID=12

Answer 1

First with CTE syntax we prepare a table with sums distribution and then using unique field Code update the main table using CASE to handle all possible ways (including first row with remainder).

Declare @Amt int;
SET @Amt=250;


with T as 
(
   SELECT ROW_NUMBER() OVER (ORDER BY Fee desc) as rn, *
   FROM tblCustData WHERE UserId=12
)  
,T2 as 
(
   SELECT *,
          ISNULL((SELECT SUM(Fee-FeePaid) FROM T WHERE T1.RN<RN),0) as PrevSum 
   FROM T as T1
 )

UPDATE
    A
SET A.FeePaid = A.FeePaid+ CASE WHEN (B.PrevSum+B.Fee-B.FeePaid<=@Amt) 
                                     AND (B.RN<>1) 
                                        THEN B.Fee-B.FeePaid
                     WHEN (B.PrevSum+B.Fee-B.FeePaid<=@Amt) AND (B.RN=1) 
                                        THEN @Amt-B.PrevSum
                     WHEN B.PrevSum>=@Amt 
                                        THEN 0                 
                     WHEN B.PrevSum+B.Fee-B.FeePaid>@Amt 
                                        THEN @Amt-B.PrevSum
              END
FROM
    tblCustData A
    JOIN T2 B ON A.Code = B.Code
GO

SQLFiddle demo

Answer 2

Try ..

declare @t table (id int identity, UserId int, Fee money, FeePaid money)

insert into @t (UserID,  Fee, FeePaid)
values
(12,      150,   0)
,(12,      100,   0)
,(12,      50 ,   0)

declare @amt money = 200;   -- change to 400 to test over paid

declare @Fees money;
select @Fees = sum(Fee) from @t;

declare @derivedt table (deid int, id int, UserId int, Fee money, FeePaid money)

insert into @derivedt (deid, id, UserId, Fee, FeePaid)
select row_number() over (order by case when @amt <= @Fees then id else -id end asc), id, UserId, Fee, FeePaid
    from @t
    -- order by case when @amt <= @Fees then id else -id end asc

; with cte(deid, id, UserId, Fee, FeePaid, Remainder)
as
(
    select 0 as deid, 0 as id, 0 as UserId, cast(0.00 as money) as Fee, cast(0.00 as money) as FeePaid , @Amt as Remainder
    from @derivedt
    where id = 1
    union all
    select t.deid, t.id, t.UserId, t.Fee, case when cte.Remainder > t.Fee then t.Fee else cte.Remainder end as FeePaid
        , case when cte.Remainder > t.Fee then cte.Remainder - t.Fee else 0 end as Remainder
    from @derivedt t inner join cte cte on t.deid = (cte.deid + 1)
)

update origt
set FeePaid = det.FeePaid
from @t origt
    inner join
    (
    select cte1.deid, cte1.id, cte1.UserId, cte1.Fee, cte1.FeePaid + ISNULL(cte2.Remainder, 0) as FeePaid
    from cte cte1
        left outer join (select top 1 deid, Remainder from cte order by deid desc) cte2
        on cte1.deid = cte2.deid
    where cte1.deid > 0
    ) det
    on origt.id = det.id

select *
from @t

Modified to continuous update of value..

    -- Create table once and insert into table once
create table #t (id int identity, UserId int, Fee money, FeePaid money)

insert into #t (UserID,  Fee, FeePaid)
values
(12,      150,   0)
,(12,      100,   0)
,(12,      50 ,   0)

-- ===============================

-- Run multiple times to populate #t table 
declare @amt money = 100;   -- change to 400 to test over paid

declare @Fees money;
select @Fees = sum(Fee - FeePaid) from #t;

declare @derivedt table (deid int, id int, UserId int, Fee money, FeePaid money)

insert into @derivedt (deid, id, UserId, Fee, FeePaid)
select row_number() over (order by case when @amt <= @Fees then id else -id end asc), id, UserId, (Fee - FeePaid) as Fee, FeePaid
    from #t
    -- order by case when @amt <= @Fees then id else -id end asc

; with cte(deid, id, UserId, Fee, FeePaid, Remainder)
as
(
    select 0 as deid, 0 as id, 0 as UserId, cast(0.00 as money) as Fee, cast(0.00 as money) as FeePaid , @Amt as Remainder
    from @derivedt
    where id = 1
    union all
    select t.deid, t.id, t.UserId, t.Fee, case when cte.Remainder >= t.Fee then t.Fee else cte.Remainder end as FeePaid
        , case when cte.Remainder >= t.Fee then cte.Remainder - t.Fee else 0 end as Remainder
    from @derivedt t inner join cte cte on t.deid = (cte.deid + 1)
)


update origt
set FeePaid = origt.FeePaid + det.FeePaid
from #t origt
    inner join
    (
    select cte1.deid, cte1.id, cte1.UserId, cte1.Fee, cte1.FeePaid + ISNULL(cte2.Remainder, 0) as FeePaid, cte1.Remainder
    from cte cte1
        left outer join (select top 1 deid, Remainder from cte order by deid desc) cte2
        on cte1.deid = cte2.deid
    where cte1.deid > 0
    ) det
    on origt.id = det.id

select *
from #t


-- Drop temp table after
-- drop table #t