Need to insert the data to PRD Table from source 1 row amount 100 with two rows chg ID: 3-30 & chg ID: 4-(100-30)=70 using SQL Server-(if fetch)

Question

Below Data Table

source:
Bill VisitAmount
1 100.00

PRD Table:
Bill ChgID outbal
1 1 30.00
1 2 100.00

Need to insert the data to PRD Table from source 1 row amount 100 with two rows chg ID: 3-30 & chg ID: 4-(100-30)=70
Bill ChgID outbal
1 1 30.00
1 2 100.00
1 3 30.00
1 4 70.00

Please advise a query.
Thank you .

Answer 1

I am not clear to what is the logic behind. I just guess you may want this:

DECLARE @Source TABLE (
    Bill int,
    VisitAmount money
);
INSERT INTO @Source VALUES (1, 100),  (2, 80);

DECLARE @PRD TABLE (
    Bill int,
    ChgID int,
    outbal money
);
INSERT INTO @PRD VALUES (1, 1, 30), (1, 2, 100), (2, 1, 20);

;WITH CTE_MAX_ChgID_by_Bill AS (
    SELECT Bill, MAX(ChgID) AS ChgID
    FROM @PRD
    GROUP BY Bill
),
CTE_Bill_ChgID_1 AS (
    SELECT Bill, ChgID, outbal
    FROM @PRD
    WHERE ChgID = 1
)

SELECT Bill, ChgID, outbal
FROM @PRD
UNION ALL 
SELECT p.Bill, 
       p.ChgID + m.ChgID AS ChgID,  
       CASE WHEN p.ChgID = 1 THEN p.outbal ELSE s.VisitAmount - c.outbal END AS outbal
FROM @Source AS s
INNER JOIN @PRD AS p ON s.Bill = p.Bill
INNER JOIN CTE_MAX_ChgID_by_Bill AS m ON p.Bill = m.Bill
INNER JOIN CTE_Bill_ChgID_1 AS c ON m.Bill = c.Bill
ORDER BY Bill, ChgID, outbal;
GO

Answer 2

Working good ! but dynamic, chg_ID =1 Hard coded...
so is it possible with using cursor/ fetch -row by row , so please advise on this