Quick window function solution, should be self explanatory

😎

USE tempdb;

GO

SET NOCOUNT ON;

IF OBJECT_ID('dbo.qamt') IS NOT NULL DROP TABLE dbo.qamt;

CREATE TABLE qamt(id INT, amt INT);

IF OBJECT_ID('dbo.qtot') IS NOT NULL DROP TABLE dbo.qtot;

CREATE TABLE dbo.qtot(id INT, tot INT);

INSERT INTO dbo.qamt

VALUES(1,10),

(1,20),

(1,30),

(2,90),

(2,10),

(3,150),

(4,20),

(4,40),

(4,50),

(4,70);

INSERT INTO dbo.qtot

VALUES(1,100),

(2,100),

(3,100),

(4,100);

;WITH ORDERED_TRANSACTION_SET AS

(

SELECT

QA.id

,ROW_NUMBER() OVER

(

ORDER BY (SELECT NULL)

) AS QA_RID

,QA.amt

FROM dbo.qamt QA

)

,RUNNING_TOTAL_SET AS

(

SELECT

QT.id

,QT.tot

,OTS.amt

,QT.tot - SUM(OTS.amt) OVER

(

PARTITION BY QT.id

ORDER BY OTS.QA_RID

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS AC_BALANCE

FROM dbo.qtot QT

OUTER APPLY ORDERED_TRANSACTION_SET OTS

WHERE QT.id = OTS.id

)

SELECT

RTS.id

,RTS.tot

,RTS.amt

,CASE

WHEN RTS.AC_BALANCE >= 0 THEN RTS.AC_BALANCE

ELSE 0

END AS RTS_BALANCE

,RTS.AC_BALANCE

FROM RUNNING_TOTAL_SET RTS;

Results

id tot amt RTS_BALANCE AC_BALANCE

----------- ----------- ----------- ----------- -----------

1 100 10 90 90

1 100 20 70 70

1 100 30 40 40

2 100 90 10 10

2 100 10 0 0

3 100 150 0 -50

4 100 20 80 80

4 100 40 40 40

4 100 50 0 -10

4 100 70 0 -80