What is general term for this problem?

I wonder if anyone can tell me the name of the general case of a particular operation. I have not seen it discussed here in my lurking.

I first saw the operation in the Allocation process of an ERP package, but the problem is easy enough to generalize. The particular thing about this problem is that it does not seem to be solvable with a single SELECT or read, it seems impossible to avoid loop-based procedural code.

Consider a table representing supply, such as production or purchase orders, and another table representing demand, such as sales orders. The task is to use a set of rules to determine for each order if supply will meet demand, and to identify unmet demand and unnecessary supply. I won't go into the various biz rules here, but they tend towards two categories. For demand, rules resolve to sorting, determining who gets considered first. The second set of rules is all about matching, determine if item substitutions are allowed, if you can match to early or late supply, and so forth.

In pseudo-code, you might express the operation as follows:

foreach (row in demand table)
{
  select rows in supply that are allowed to match

  foreach (row in matching supply table)   {
    update row in demand, reduce demand by       amount of supply, up to amount of demand     update row in original supply, reducing available supply     if (demand = 0) exit loop
  }
  if (demand > 0)

     Insert into table of unmet demand
}
insert into unneeded_supply select from supply where qty > 0

The reason this cannot be solved without a loop is that the JOIN between one supply row and one demand row actually results in a WRITE to both rows, which affects the next JOIN you do. Therefore you must evaluate row by row.

Can anybody fill me in on the name of this problem in the general case?

I have little hope that there is actually a set-based solution, but if I have missed something and there is, that would be good news.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me