T-SQL - Update column with calculated values from previous rows

Question

Here is my sample data

    declare @MyTable TABLE(ClientId int, RecNo int, PaidAmt decimal(12, 2), PaidBefore decimal(12,2),
    ReimbToDate decimal(12,2), CanReimb decimal(12,2), BalBeforeReimb decimal(12, 2), 
    ReimbAmt decimal(12, 2), BalAfterReimb decimal(12, 2) )

insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate, CanReimb, BalBeforeReimb ) select 1, 1, 100, 0, 0, 0 ,250
insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate) select 1, 2, 400, 0, 0
insert into @MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate) select 1, 3,-200 ,0 ,0

insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate ,CanReimb,BalBeforeReimb) select 2 ,1 ,900 ,0 ,0 ,0 ,3000
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,2 ,300 ,0 ,0
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,3 ,1600 ,0 ,0
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,4 ,-900 ,900 ,900
insert into @MyTable (ClientId ,RecNo ,PaidAmt ,PaidBefore ,ReimbToDate) select 2 ,5 ,-300 ,300 ,300
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,6,750,0,0
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,7,625,0,0
insert into @MyTable (ClientId ,RecNo,PaidAmt,PaidBefore,ReimbToDate) select 2,8,125,625,625

Other Columns are calculated as follow:

CanReimb = PaidAmt + PaidBefore - ReimbToDate

BalBeforeReimb is BalAfterReimb of previous row of same client when RecNo is not 1

ReimbAmt is minimum of CanReimb and BalBeforeReimb

BalAfterReimb = BalBeforeReimb - ReimbAmt

I need output as follow:

Can anyone please help?

hmm, but where do you get balbeforereimb for first row? is that the only one that is "set" and rest must be calculated? — siggemannen, May 10 '23 at 22:04
I do not understand the test data or results but: 1. Due to calculations being dependant on previous calcuations, Window functions will not work. 2. I would be inclined to do this with a DataReader in the middle tier. 3. If you really need to do this with t-sql then use a cursor, recursion or the Quirky Update. The Quirky update is likly to be the fastest t-sql solution but be aware it is non-relational and undocumented by Microsoft so is not officially supported. Caveat Emptor! — Aardvark, May 11 '23 at 10:02
https://www.sqlservercentral.com/articles/solving-the-running-total-and-ordinal-rank-problems-rewritten — Aardvark, May 11 '23 at 10:02
As a clustered index is required use the following: DROP TABLE IF EXISTS #MyTable; CREATE TABLE #MyTable ( ClientId int NOT NULL ,RecNo int NOT NULL ,PaidAmt decimal(12, 2) NOT NULL ,PaidBefore decimal(12,2) NOT NULL ,ReimbToDate decimal(12,2) NOT NULL ,CanReimb decimal(12,2) NULL ,BalBeforeReimb decimal(12, 2) NULL ,ReimbAmt decimal(12, 2) NULL ,BalAfterReimb decimal(12, 2) NULL -- Need a clustered index for Quirky Upate to work ,PRIMARY KEY (ClientId, RecNo) ); — Aardvark, May 11 '23 at 10:03
INSERT INTO #MyTable (ClientId, RecNo, PaidAmt, PaidBefore, ReimbToDate, BalBeforeReimb) VALUES (1, 1, 100, 0, 0, 250) ,(1, 2, 400, 0, 0, NULL) /* Assuming PaidBefore should be 400 not 0 ReimbToDate should be 150 not 0 */ ,(1, 3,-200 ,400 ,150, NULL) ,(2 ,1 ,900 ,0 ,0, 3000) ,(2 ,2 ,300 ,0 ,0, NULL) ,(2 ,3 ,1600 ,0 ,0, NULL) ,(2 ,4 ,-900 ,900 ,900, NULL) ,(2 ,5 ,-300 ,300 ,300, NULL) ,(2,6,750,0,0, NULL) ,(2,7,625,0,0, NULL) ,(2,8,125,625,625, NULL); select * from #MyTable; — Aardvark, May 11 '23 at 10:04
DECLARE @CanReimb decimal(12, 2) ,@BalBeforeReimb decimal(12, 2) ,@BalAfterReimb decimal(12, 2) ,@ReimbAmt decimal(12, 2) ,@check int ,@ClientId int ,@RecNo int; — Aardvark, May 11 '23 at 10:05
@siggemannen - yes balforreimb is coming from other table for recno = 1 for each client. — mak101, May 11 '23 at 13:11

score 0 · Answer 1 · answered May 10 '23 at 21:18

0

I wrote the code based on your request, because I don't know the data, I didn't test the data


select 
ClientId
,RecNo
,PaidAmt
,PaidBefore
,CanReimb
,BalBeforeReimb
,ReimbAmt=case when CanReimb<BalBeforeReimb then CanReimb else BalBeforeReimb end,
BalAfterReimb=BalBeforeReimb-ReimbAmt

from (
        select ClientId,RecNo,PaidAmt,PaidBefore,CanReimb,ReimbAmt,case when  RecNo<>1 then 
        LAG (BalBeforeReimb) over (partition by ClientId order by ClientId) else 0 end as BalBeforeReimb

        from @MyTable
)a

dbfiddle

answered May 10 '23 at 21:18

abolfazl sadeghi

2,277
2
12
20

No, it does not give the results as desired. It turns all BalAfterReimb null, which miscalculate other columns too – mak101 May 10 '23 at 21:55
please example data because, CanReimb and BalAfterReimb is wrong (CanReimb use example with calculation) – abolfazl sadeghi May 10 '23 at 22:09
Sorry, forgot to add CanReimb Formula, CanReimb = PaidAmt + PaidBefore - ReimbToDate - I am editing in original post too. – mak101 May 10 '23 at 23:01
your example data with result is different(example: Row 3 of PaidBefore in pic =400 and PaidBefore of example =0) please check whole base data(ClientId,RecNo,PaidAmt,PaidBefore,ReimbAmt) – abolfazl sadeghi May 10 '23 at 23:21
CanReimb = PaidAmt + PaidBefore - ReimbToDate = -200 + 400 - 150 = 50 BalBeforeReimb = Previous Row BalAfterReimb = 0 ReimbAmt = minimum of CanReimb and BalBeforeReimb = 0 BalAfterReimb = BalBeforeReimb - ReimbAmt = 0 - 0 = 0 – mak101 May 10 '23 at 23:45
I understand the formula. Your sample data is different from the final result. Do this query and you will understand . select PaidAmt , PaidBefore , ReimbToDate from @MyTable where ClientId=1 and RecNo=3 – abolfazl sadeghi May 11 '23 at 00:01

T-SQL - Update column with calculated values from previous rows

1 Answers1