Reading through multiple records, with Loop capability

[chalrav]

Hi,

I have three tables as below:

Table: Demand
ITEM_ID, QUANTITY
Item A, 10

Table: Route
ITEM_ID, OPERATION_NO, RESOURCE, TIME
Item A, 10, RES1, 60
Item A, 20, RES2, 120
Item A, 30, RES3, 60

Table: Load

RESOURCE, LOAD
RES1, 600
RES2, 1200
RES3 , 600

The values in LOAD field of the LOAD table are calculated values.

What I want to achieve?
I want to calculate the LOAD field in the LOAD table

Steps:
1) Select the records for every ITEM_ID (e.g. Item A ) in the Route table.
2) Start with the lowest number of Operation_No (e.g. Operation_No 10), check the following:
a. Value in the RESOURCE field (e.g. Res1) and value in the TIME field (e.g. 60).
3) Multiply the value in the ROUTE.TIME field with the corresponding value in DEMAND.QUANTITY. (ROUTE.ITEM_ID = DEMAND.ITEM_ID)
4) Update the Table Load with the following values:
a. RES1 in field RESOURCE
b. The multiplied product we got in Step 3 above in the LOAD.LOAD field.
5) Go back to Step 2 above and select the next lowest record for Item A ( Operation_No = 20 in this case) and perform the following steps:
a. Step 2.a
b. Step 3
c. Step 4
6) Go back to Step 2. Continue this iteration (loop) until we complete all the records for this item. (In this case, there are three records for Item A: Operation_No: 10, 20 & 30).
7) Once all the records for Item A are processed and the LOAD table is updated, then go back to Step 1 and select the next item.

[MMcCarthy]

Hi,

I have three tables as below:

Table: Demand
ITEM_ID, QUANTITY
Item A, 10

Table: Route
ITEM_ID, OPERATION_NO, RESOURCE, TIME
Item A, 10, RES1, 60
Item A, 20, RES2, 120
Item A, 30, RES3, 60

Table: Load

RESOURCE, LOAD
RES1, 600
RES2, 1200
RES3 , 600

The values in LOAD field of the LOAD table are calculated values.

What I want to achieve?
I want to calculate the LOAD field in the LOAD table

Steps:
1) Select the records for every ITEM_ID (e.g. Item A ) in the Route table.
2) Start with the lowest number of Operation_No (e.g. Operation_No 10), check the following:
a. Value in the RESOURCE field (e.g. Res1) and value in the TIME field (e.g. 60).
3) Multiply the value in the ROUTE.TIME field with the corresponding value in DEMAND.QUANTITY. (ROUTE.ITEM_ID = DEMAND.ITEM_ID)
4) Update the Table Load with the following values:
a. RES1 in field RESOURCE
b. The multiplied product we got in Step 3 above in the LOAD.LOAD field.
5) Go back to Step 2 above and select the next lowest record for Item A ( Operation_No = 20 in this case) and perform the following steps:
a. Step 2.a
b. Step 3
c. Step 4
6) Go back to Step 2. Continue this iteration (loop) until we complete all the records for this item. (In this case, there are three records for Item A: Operation_No: 10, 20 & 30).
7) Once all the records for Item A are processed and the LOAD table is updated, then go back to Step 1 and select the next item.

Can you post a reply to this question on Wednesday/Thursday to bump it up the list as it may not get much attention over the holidays.

Merry Christmas

Mary

[NeoPa]

Try this :

SELECT R.Resource,Sum(D.Quantity * R.Time) AS Load
FROM Demand AS D
  INNER JOIN Route AS R
  ON (D.Item_ID=R.Item_ID)
GROUP BY R.Resource
ORDER BY R.Resource

It won't update the Load table (not a good idea) but will return the records you need as the results of a query.