Calculating a value in SQL using previous row's values and then using current row value for future calculations

Question

In SQL Server, I have to perform calculations on the following table:

Row#	Period_begin_dt	Period_end_dt	Amount	Period_Status	Time_Period
1	2023-07-01	2023-09-30	0	Delinq	Quarterly
2	2023-04-01	2023-06-30	0	Delinq	Quarterly
3	2023-01-01	2023-03-31	0	Delinq	Quarterly
4	2022-10-01	2022-12-31	10	Active	Quarterly
5	2022-07-01	2022-09-30	20	Active	Quarterly
6	2022-04-01	2022-06-30	10	Active	Quarterly
7	2022-01-01	2022-03-31	20	Active	Quarterly

Fig.1

Logic: Find amount where Period_Status is 'Delinq' and Amount is '0'from earliest period_end_dt (here 2023-03-31) till last period_end_dt (here 2023-03-31)

E.g. Calculate the Amount required for Row Number 3 where period status is 'Delinq'

1. I used dense_rank to find the first time period -start date-‘2023-03-31’ where Period_Status is 'Delinq' and dense rank = 1:

Row#	Period_begin_dt	Period_end_dt	Amount	Period_Status	Dense_Rank	Time_Period
1	2023-07-01	2023-09-30	0	Delinq	3	Quarterly
2	2023-04-01	2023-06-30	0	Delinq	2	Quarterly
3	2023-01-01	2023-03-31	0	Delinq	1	Quarterly
4	2022-10-01	2022-12-31	10	Active	1	Quarterly
5	2022-07-01	2022-09-30	20	Active	2	Quarterly
6	2022-04-01	2022-06-30	10	Active	3	Quarterly
7	2022-01-01	2022-03-31	20	Active	4	Quarterly

Fig.2

dense_rank() over (partition by period_status order by period_end_dt )as denserank

Then find end date- by reducing a year.

set @end = dateadd(yyyy,-1, @start)

2. Average amounts across all periods (going back a year) between period_end_dt ‘2023-03-31’ and period_end_dt ‘2022-03-31’. Here- (20+10+20+10)/4 gives amount $15 - Amount A
3. Also, find Value in period_end_dt ‘2022-03-31’ which gives amount $20 - Amount B
4. Take the higher of the two amounts from Amount A and Amount B respectively. Here we get Amount =$20 as that’s the larger value.
5. Substitute this amount in Row Number 3 where period status is 'Delinq'
   Now using this amount in Row Number 3, I need to repeat the entire above process for Row Number 2 where dense_rank = 2 between period_end_dt ‘2023-06-30’ and ‘2022-06-30’.

I reviewed other questions on same but my fig.1 results are also coming from a sql query and I dont have the ability to update table (read-only permissions on database) so my best bet will perhaps be a temporary table. Can someone please help me? Any insights will be appreciated.

Question- I want to know the new amounts for row 2 and row 1 respectively.

Repeat process for row 2- I got my amount value in row 3 as $20 , then I need to calculate the amount for row 2, repeating the entire process again between Period_end_dt (between 2023-06-30 and 2022-06-30 ). Here the average Amount A (average)will be (20+10+20+10)/4 =$15 while amount B (for 2022-06-30)will be $ 10 so I pick the higher of the two amounts. New amount in row 2 will be $15.

Repeat process for row 1- I got my amount value in row 2 as $15. Again between Period_end_dt (between 2023-09-30 and 2022-09-30 ). Here the average Amount A (average)will be (15+20+10+20)/4=$16 while amount B (for 2022-06-30)will be $ 20 so I pick the higher of the two amounts. New amount in row 1 will be $20. But I do not know how to write the code for it or where to get started on the same. Hope this makes sense and sorry for incomplete question at first.

Desired Result: Row 3 amount calculated as $20 Row 2 amount calculated as $15 Row 2 amount calculated as $20

Row#	Period_begin_dt	Period_end_dt	Amount	Period_Status	Time_Period
1	2023-07-01	2023-09-30	20	Delinq	Quarterly
2	2023-04-01	2023-06-30	15	Delinq	Quarterly
3	2023-01-01	2023-03-31	20	Delinq	Quarterly
4	2022-10-01	2022-12-31	10	Active	Quarterly
5	2022-07-01	2022-09-30	20	Active	Quarterly
6	2022-04-01	2022-06-30	10	Active	Quarterly
7	2022-01-01	2022-03-31	20	Active	Quarterly

EDIT- For different time periods like monthly, annualy,quarterly, can I use case statements to seed the rows like @TN proposed in his answer. Is there a way where I can incorporate for different time periods with case statement like  select coalesce(

1. case when p.Period_Status = Delinq and p.Time_Period = 'Annual' then ( seed only one previous row)

2. case when p.Period_Status = Delinq and p.Time_Period = 'Quarterly'  then ( seed 4 previous rows)

3. case when p.Period_Status = Delinq and p.Time_Period = 'Monthly'  then ( seed 12 previous rows) )