I have a data.table

dt <- data.table(
   Date = c("20240701", "20240801", "20240901", "20241001"),
   Plan          = c(85,17,50, 34),
   OpenPlan      = c(85, 0,33, 0),
   ValuetoReduce = c(97,97,97,97)
  )

I want to reduce “Plan” over all periods in total by 97 (ValuetoReduce)

In my mind this looks like:

• Row number 1: I can reduce the “Plan” by 85 (OpenPlan). The NewPlan then is 0. As I consumed 85 I have 12 left which i can reduce in the rows
• Row number 2: Here OpenPlan is 0, so I can’t reduce anything, so set the “NewPlan” as “Plan”.
• Row number 3: Here I can reduce 33 (OpenPlan), but I only have 12 left. So reduce the 50 (Plan) by 12 as NewPlan
• Following rows: Nothing to reduce anymore, so set the NewPlan equal to Plan

New dt should like look like

dt_new <- data.table(
  Date = c("20240701", "'20240801", "20240901", "20241001"),
  Plan          = c(85,17,50, 34),
  OpenPlan      = c(85, 0,33, 0),
  ValuetoReduce = c(97,97,97,97),
  NewPlan       = c(0,17,38,34)
 )

Thanks!