Shyam Shyam - 6 months ago 9
SQL Question

Split one row into two based on other table row value

I have two table as below

First Table



CID Cmonth Amount
---------------------------
1 JAN 15000
2 JAN 5000
3 JAN 5000
4 FEB 20000
5 MAR 15000


Second Table



SerialNo EmpID Cmonth Amount
--------------------------------------
1 111 JAN 11000
2 222 JAN 7000
3 333 JAN 7000
4 111 FEB 10000
5 222 FEB 5000
6 333 FEB 5000
7 111 MAR 10000
8 222 MAR 5000
9 333 MAR 5000


Expected Output



SerialNo EmpID Cmonth Amount CID
-----------------------------------------------
1 111 JAN 11000 1
2 222 JAN 4000 1
2 222 JAN 3000 2
3 333 JAN 2000 2
3 333 JAN 5000 3
4 111 FEB 10000 4
5 222 FEB 5000 4
6 333 FEB 5000 4
7 111 MAR 10000 5
8 222 MAR 5000 5
9 333 MAR 5000 5


I don't want to use cursor or while loop. Is there any other way. Open for any suggestion.

Answer

This gets you most of the way there. There might be an easier/faster/better way to do this with a recursive CTE, but this approach was the first thing that I tried.

There's currently one "bug", but you didn't seem to address this case in your question. The buckets for March only come to 15000, but the amounts total 20000 for that month. In your final output you assigned the last amount to the last CID, but that isn't clear from any explanation, so for now I've left this as-is:

;WITH CTE_RunningTotals AS
(
    SELECT
        SerialNo,
        EmpID,
        Cmonth,
        Amount,
        SUM(Amount) OVER (PARTITION BY Cmonth ORDER BY SerialNo) AS RunningAmount
    FROM
        Deposits
),
CTE_RunningTotalsWithLast AS
(
    SELECT SerialNo, EmpID, Cmonth, Amount, RunningAmount, LAG(RunningAmount, 1, 0) OVER (PARTITION BY Cmonth ORDER BY SerialNo) AS LastRunningAmount FROM CTE_RunningTotals
),
CTE_LimitTotals AS
(
    SELECT CID, Cmonth, SUM(Amount) OVER (PARTITION BY Cmonth ORDER BY CID) AS RunningAmount FROM Limits
),
CTE_LimitTotalsWithLast AS
(
    SELECT
        CID, Cmonth, RunningAmount,
        LAG(RunningAmount, 1, 0) OVER (PARTITION BY Cmonth ORDER BY CID) AS LastRunningAmount,
        LAG(CID) OVER (PARTITION BY Cmonth ORDER BY CID) AS LastCID
    FROM
        CTE_LimitTotals
)
SELECT
    D.SerialNo,
    D.EmpID,
    D.Cmonth,
    CASE WHEN D.Amount < D.RunningAmount - L.LastRunningAmount THEN D.Amount ELSE D.RunningAmount - L.LastRunningAmount END AS Amount,
    L.CID
FROM
    CTE_RunningTotalsWithLast D
INNER JOIN CTE_LimitTotalsWithLast L ON
    L.Cmonth = D.Cmonth AND
    D.RunningAmount > L.LastRunningAmount AND
    D.RunningAmount <= L.RunningAmount
UNION ALL
SELECT
    D.SerialNo,
    D.EmpID,
    D.Cmonth,
    L.LastRunningAmount - D.LastRunningAmount,
    L.LastCID AS CID
FROM
    CTE_RunningTotalsWithLast D
INNER JOIN CTE_LimitTotalsWithLast L ON
    L.Cmonth = D.Cmonth AND
    D.LastRunningAmount < L.LastRunningAmount AND
    D.RunningAmount >= L.LastRunningAmount
ORDER BY
    D.SerialNo
Comments