In SQL, split an amount among multiple rows

Question

In our application we are using SQL Server 2012. I come across a situation, where I am not able to come up with a single query (without loop) that will solve my purpose. Let me explain it.

I have a Travel table -

ID       State      Days_Count   Remaining_Days
1         AL          20            -
2         AL          2             -
3         AL          14            -

I have another table for Travel_Sum

State      Days_Sum   Max_Limit    Exceeding_Amt
AL           36          12             24

There are more entries for other States. What I am trying is to write a single query that will update remaining days with Exceeding_Amt. In this example value of Exceeding_Amt column is 24. So in the first table, I'm trying to update value 20 (max value of days_Count) against remaining days, 2 against second row and (24-20-2) 2 against last row.

Basically I have to split exceeding amount (ie 24) among rows of first table where state matches. So to split 24, I want to update remaining_days of first row as 20. Because I can not update with a value that is more than days_count. So I have 4 days left. For second row, Days_count is 2. So I have updated this row with 2 (max value which I'm allowed to enter). In third row I have updated Remaining_Days column value with remaining 2 days.

Desired output :

ID       State      Days_Count   Remaining_Days
1         AL          20            20
2         AL          2             2
3         AL          14            2

This question might be confusing. If so, please let me know. I'll try my best to give a proper understanding.

Answer 1

The following should work for you:

UPDATE  t
SET     Remaining_Days = CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
                                WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
                                ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
                            END
FROM    (   SELECT  t.ID,
                    t.Days_Count,
                    t.[State],
                    CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID),
                    t.Remaining_Days,
                    ts.Exceeding_Amt
            FROM    Travel AS t
                    INNER JOIN Travel_Sum AS ts
                        ON ts.[State] = t.[State]
        ) AS t;

The main step here is to get the cumualtive number of days, using the windowed function SUM() OVER():

SELECT  t.ID,
        t.Days_Count,
        CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID)
FROM    Travel AS t;

This gives:

ID  Days_Count  State   CumulativeDayCount
1   20          AL      20
2   2           AL      22
3   14          AL      36

Then you can join to your sum table, to get the Exceeding_Amt column, and work out whether all, some, or none of this should be applied based on the cumulative total. This is where the case expression comes in:

CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
    WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
    ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
END

If you just need a select statement, and don't actually need to update your table you can just use:

SELECT  t.ID,
        t.State,
        t.Days_Count,
        Remaining_Days = CASE WHEN CumulativeDayCount < Exceeding_Amt THEN Days_Count
                                WHEN (CumulativeDayCount - Days_Count) > Exceeding_Amt THEN 0
                                ELSE Exceeding_Amt - (CumulativeDayCount - Days_Count)
                            END
FROM    (   SELECT  t.ID,
                    t.Days_Count,
                    t.[State],
                    CumulativeDayCount = SUM(t.Days_Count) OVER(PARTITION BY t.[State] ORDER BY t.ID),
                    ts.Exceeding_Amt
            FROM    Travel AS t
                    INNER JOIN Travel_Sum AS ts
                        ON ts.[State] = t.[State]
        ) AS t;

Example on SQL Fiddle

Answer 2

The following uses CTE approach to run the calculate the totals.

DECLARE @Travel TABLE (ID INT, State VARCHAR(10), Days_Count INT)
DECLARE @Travel_Sum TABLE (State VARCHAR(10), Exceeding_Amt INT)

INSERT @Travel VALUES (1, 'AL', 20), (2, 'AL', 2), (3, 'AL', 14)
INSERT @Travel_Sum VALUES ('AL', 24)

;WITH TravelRows AS (
    SELECT
        ID,
        State,
        Days_Count,
        ROW_NUMBER() OVER (PARTITION BY State ORDER BY ID) AS RowNum
    FROM @Travel
), CTE AS (
    SELECT
        TR.ID,
        TR.State,
        TR.Days_Count,
        TR.RowNum,
        TS.Exceeding_Amt - TR.Days_Count AS Exceeding_Amt,
        CASE WHEN TS.Exceeding_Amt > TR.Days_Count THEN TR.Days_Count ELSE TS.Exceeding_Amt END AS Remaining_days
    FROM TravelRows TR
        INNER JOIN @Travel_Sum TS 
            ON TR.State = TS.State
    WHERE RowNum = 1
    UNION ALL
    SELECT
        R2.ID,
        R2.State,
        R2.Days_Count,
        R2.RowNum,
        R1.Exceeding_Amt - R2.Days_Count,
        CASE WHEN R1.Exceeding_Amt > R2.Days_Count THEN R2.Days_Count ELSE R1.Exceeding_Amt END AS Remaining_days
    FROM CTE R1
        INNER JOIN TravelRows R2
            ON R1.State = R2.State
                AND R1.RowNum + 1 = R2.RowNum
)
    SELECT
        ID,
        State,
        Days_Count,
        Remaining_Days
    FROM CTE
    OPTION (MAXRECURSION 0)

Output:

ID          State      Days_Count  Remaining_Days
----------- ---------- ----------- --------------
1           AL         20          20
2           AL         2           2
3           AL         14          2