Analytical Function in Query, select against budget, CSUM

Database
Enthusiast

Analytical Function in Query, select against budget, CSUM

Dear All,

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. 

  • My total budget is 100 million. There are 8 projects for which cost & profitability are mentioned against each.
  • Projects must be selected in profitability order (highest to lowest).

Current 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.

Required Scenario

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.

 

 

Teradata envelope.JPG

  • analytical
  • csum
  • functions
  • preceding
  • unbounded
1 REPLY
Junior Contributor

Re: Analytical Function in Query, select against budget, CSUM

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.