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

Question

I have a leave table in the following format:

╔════╦═══════════╦═════════════════════╦═════════╦══════════╗
║ ID ║ Available ║ MaximumAccumulation ║ Availed ║ Priority ║
╠════╬═══════════╬═════════════════════╬═════════╬══════════╣
║  1 ║        10 ║                   4 ║       0 ║        1 ║
║  2 ║        15 ║                   5 ║       0 ║        2 ║
║  3 ║         8 ║                   3 ║       0 ║        3 ║
╚════╩═══════════╩═════════════════════╩═════════╩══════════╝

Now, if a user apply for 10 leaves the leaves must start to deduct from first row(Till MaximumAccumulation Limit), after the deduction if still the user has leaves left to be accumulated(in our case he is still left with 6 leaves) it should be deducted from the next row. This process will go on until all the rows have been traversed. Now if he is still left with leaves, A new row has to be inserted in the table with:

ID: 0, Available: 0, MaximumAccumulation: 0, Availed(Whatever is left) Priority: 0

In our case the output will be:

╔════╦═══════════╦═════════════════════╦═════════╦══════════╗
║ ID ║ Available ║ MaximumAccumulation ║ Availed ║ Priority ║
╠════╬═══════════╬═════════════════════╬═════════╬══════════╣
║  1 ║         6 ║                   4 ║       4 ║        1 ║
║  2 ║         9 ║                   5 ║       6 ║        2 ║
║  3 ║         8 ║                   3 ║       0 ║        3 ║
╚════╩═══════════╩═════════════════════╩═════════╩══════════╝

Could someone help me to write such a query?

Answer 1

I couldn't really think of a set-based method to do this, so I went procedural instead! (I'd advise doing this code-side and not database side if I were you though!)

The following stored proc takes your input and calculates by row how much can be taken off through a while loop. It does this for each row until it either runs out of rows (at which point it will insert a new row with the remainder), or it has no more days to take off.

ALTER PROCEDURE [dbo].[UpdateLeave] @Days int
AS 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 to check if the cursor is on the last row
DECLARE @RowCount INT = (SELECT COUNT(*) FROM Leave), @CurrentRow INT = 1

--Cursor to loop through the rows
DECLARE CURS CURSOR FOR SELECT ID, Available, MaxAccum, Availed, [Priority] FROM Leave WHERE Available > 0
OPEN CURS
FETCH NEXT FROM CURS INTO @ID, @Available, @MaxAccum, @Availed, @Priority
WHILE @@FETCH_STATUS = 0
BEGIN

    --1. Use a while loop to take off as many days as possible from the current row.
    WHILE @RowAccumSoFar <= @MaxAccum
    BEGIN
        UPDATE Leave 
        SET Available -= 1, Availed += 1
        WHERE ID = @ID

        SET @RowAccumSoFar += 1
        SET @TotalAccumSoFar += 1

        IF(@TotalAccumSoFar = @Days)
        BEGIN
            BREAK
        END
    END

    --2. Check if we've taken off all the leave days
    IF(@TotalAccumSoFar = @Days)
    BEGIN
        BREAK
    END

    --3. If not and we're on the last row, insert a new row.
    IF(@RowCount = @CurrentRow)
    BEGIN
        INSERT INTO Leave (Available, MaxAccum, Availed, [Priority]) VALUES (0,0,(@Days - @TotalAccumSoFar),0)
    END

    --4. Update variables
    SET @CurrentRow += 1
    SET @RowAccumSoFar = 0

FETCH NEXT FROM CURS INTO @ID, @Available, @MaxAccum, @Availed, @Priority
END
CLOSE CURS
DEALLOCATE CURS
END

The only difference I have is that on my Leaves table, the ID is an IDENTITY(1,1) column, so I don't have to handle the ID on the insert.

Results

+----+-----------+--------------+---------+----------+
| id | Available | MaximumAccum | Availed | priority |
+----+-----------+--------------+---------+----------+
|  1 |         6 |            4 |       4 |        1 |
|  2 |         9 |            5 |       6 |        2 |
|  3 |         8 |            3 |       0 |        3 |
+----+-----------+--------------+---------+----------+

Answer 2

It`s a bit verbose but it works.Tested on my machine

CREATE TABLE [dbo].[T](
    [Id] [int] NULL,
    [Available] [int] NULL,
    [MaximumAccumulation] [int] NULL,
    [Availed] [int] NULL,
    [Priority] [int] NULL
) ;


INSERT [dbo].[T] ([Id], [Available], [MaximumAccumulation], [Availed], [Priority]) VALUES (1, 10, 4, 0, 1)

INSERT [dbo].[T] ([Id], [Available], [MaximumAccumulation], [Availed], [Priority]) VALUES (2, 15, 5, 0, 2)

INSERT [dbo].[T] ([Id], [Available], [MaximumAccumulation], [Availed], [Priority]) VALUES (3, 8, 3, 0, 3)

Query

WITH CTE AS
  (SELECT *,
          CASE
              WHEN LAG(t.Available,1) OVER(
                                           ORDER BY t.id) IS NULL THEN (10 - t.MaximumAccumulation)
              WHEN LEAD(t.Available,1) OVER(
                                            ORDER BY t.id) IS NULL THEN t.Available
              ELSE t.Available - (10 - LAG(t.MaximumAccumulation,1) OVER(
                                                                         ORDER BY t.id))
          END AS NewAvailable,
          t.Available - CASE
                            WHEN LAG(t.Available,1) OVER(
                                                         ORDER BY t.id) IS NULL THEN (10 - t.MaximumAccumulation)
                            WHEN LEAD(t.Available,1) OVER(
                                                          ORDER BY t.id) IS NULL THEN t.MaximumAccumulation
                            ELSE t.Available - (10 - LAG(t.MaximumAccumulation,1) OVER(
                                                                                       ORDER BY t.id))
                        END AS NewAvailed
   FROM t),
     CTE2 AS
  (SELECT *,
          SUM(NewAvailed) OVER (
                                ORDER BY id) AS rollingsum
   FROM CTE)
SELECT t.id,
       CASE
           WHEN rollingsum<=10 THEN NewAvailable
           ELSE t.Available
       END as Available,
       t.MaximumAccumulation,
       CASE
           WHEN rollingsum<=10 THEN NewAvailed
           ELSE t.Availed
       END as Availed,
       t.priority
FROM CTE2
JOIN t ON CTE2.id=t.id

Results

id  Available   MaximumAccumulation Availed priority
1   6                 4               4         1
2   9                 5               6         2
3   8                 3               0         3