I have case for which i have put the scenarios in the attached pic below. I need some technical advice how can i implement the new required scenario.
Right now, we are sorting the projects in order of profitability.
Cumulative cost is calculated using (Unbounded Preceding).
Once Cumulative cost crosses budget, that project will not be selected.
In such case projects A,B,C will be selected. because D onwards, the cumulative sum will exceed the budget.
In the above case, skip project-D (as it exceeds budget), & consider lower budget projects below it which can fit into budget.
In such case E,F will be considered & G will be rejected (as G's cost makes spending 103 million).
In this case, skip G. we have spent 98 million & 2 million left. Hence H will be selected.
Afaik there's no way to calculate this using OLAP functions (btw, CSUM is deprecated, better use SUM OVER ROWS UNBOUNDED PRECEDING).
Possible solution are based on sequential processing: SP with cursor/loop or a recursive CTE.