sql - How to get remaining percentages for column? -

i have table formatted following, we'll call "payments"

p_id | p_paynum | p_amtperc | p_type 1    |    1     |   100     | fp1 2    |    1     |   50      | 2p1 3    |    2     |   50      | 2p1 4    |    1     |   25      | 4p1 5    |    2     |   0       | 4p1 6    |    3     |   0       | 4p1 7    |    4     |   0       | 4p1 

the question table working with, trying come best way create update script using @temptables , inserts find values amtperc = 0 , figure out how take current non-0 (in case 25 4p1 type) , figure out remaining 3 0 (which 25; 25*4 = 100).

there couple of entries in table payment plans of quarterly, semi-quarterly, semi-annually, bi-monthly, etc. same few suffer issue first partial-percent present (the 1st payment) , rest 0. trying find best way dynamically find 0's, find 1st payment represent set , have update correct percentages total 100.

i unsure of how word better , hopeful understands mean. if there better way phrase or parse question, feel free modify make more sense , maybe code discovered can else figuring out remaining partial percents totaling 100.

at sounds want not compute missing payment percentages, update base table them. in sql server, can accomplish way:

update p set p.p_amtperc = r.remaining_perc / r.num_zero   payments p   inner join (       select         p_type,         100 - sum(p_amtperc) remaining_perc,         sum(case p_amtperc when 0 1 else 0 end) num_zero       payments       group p_type       having sum(p_amtperc) < 100         , sum(case p_amtperc when 0 1 else 0 end) > 0     ) r     on p.p_type = r.p_type p.p_amtperc = 0 

you recognize inline view similar queries presented in 2 other answers posted far. computes each payment type percentage remaining allocated , number of payment rows among split it, filtering out payment types (at least) 100% payment allocated, or there no rows 0 payment specified.

the rest of query proprietary sql server syntax for, basically, updating table via view. updates rows have p_amtperc = 0 and have corresponding row in inline view. in particular, if there payment type recorded payments add @ least 100, has zero-percent payments, rows payment type updated. ignores non-zero payment percentages, splitting balance among zero-percent payments instead of making them match first payment.