SQL Server-Query to traverse rows of table and updating sequentially -

i have leave table in following format:

╔════╦═══════════╦═════════════════════╦═════════╦══════════╗ ║ id ║ available ║ maximumaccumulation ║ availed ║ priority ║ ╠════╬═══════════╬═════════════════════╬═════════╬══════════╣ ║  1 ║        10 ║                   4 ║       0 ║        1 ║ ║  2 ║        15 ║                   5 ║       0 ║        2 ║ ║  3 ║         8 ║                   3 ║       0 ║        3 ║ ╚════╩═══════════╩═════════════════════╩═════════╩══════════╝ 

now, if user apply 10 leaves leaves must start deduct first row(till maximumaccumulation limit), after deduction if still user has leaves left accumulated(in our case still left 6 leaves) should deducted next row. process go on until rows have been traversed. if still left leaves, new row has inserted in table with:

id: 0, available: 0, maximumaccumulation: 0, availed(whatever left) priority: 0

in our case output be:

╔════╦═══════════╦═════════════════════╦═════════╦══════════╗ ║ id ║ available ║ maximumaccumulation ║ availed ║ priority ║ ╠════╬═══════════╬═════════════════════╬═════════╬══════════╣ ║  1 ║         6 ║                   4 ║       4 ║        1 ║ ║  2 ║         9 ║                   5 ║       6 ║        2 ║ ║  3 ║         8 ║                   3 ║       0 ║        3 ║ ╚════╩═══════════╩═════════════════════╩═════════╩══════════╝ 

could me write such query?

i couldn't think of set-based method this, went procedural instead! (i'd advise doing code-side , not database side if though!)

the following stored proc takes input , calculates row how can taken off through while loop. each row until either runs out of rows (at point insert new row remainder), or has no more days take off.

alter procedure [dbo].[updateleave] @days int begin  --cursor variables. declare @id int, @available int, @maxaccum int, @availed int, @priority int  --inner while-loop variables declare @totalaccumsofar int = 0, @rowaccumsofar int = 1  --variable check if cursor on last row declare @rowcount int = (select count(*) leave), @currentrow int = 1  --cursor loop through rows declare curs cursor select id, available, maxaccum, availed, [priority] leave available > 0 open curs fetch next curs @id, @available, @maxaccum, @availed, @priority while @@fetch_status = 0 begin      --1. use while loop take off many days possible current row.     while @rowaccumsofar <= @maxaccum     begin         update leave          set available -= 1, availed += 1         id = @id          set @rowaccumsofar += 1         set @totalaccumsofar += 1          if(@totalaccumsofar = @days)         begin             break         end     end      --2. check if we've taken off leave days     if(@totalaccumsofar = @days)     begin         break     end      --3. if not , we're on last row, insert new row.     if(@rowcount = @currentrow)     begin         insert leave (available, maxaccum, availed, [priority]) values (0,0,(@days - @totalaccumsofar),0)     end      --4. update variables     set @currentrow += 1     set @rowaccumsofar = 0  fetch next curs @id, @available, @maxaccum, @availed, @priority end close curs deallocate curs end 

the difference have on leaves table, id identity(1,1) column, don't have handle id on insert.

results

+----+-----------+--------------+---------+----------+ | id | available | maximumaccum | availed | priority | +----+-----------+--------------+---------+----------+ |  1 |         6 |            4 |       4 |        1 | |  2 |         9 |            5 |       6 |        2 | |  3 |         8 |            3 |       0 |        3 | +----+-----------+--------------+---------+----------+