By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,145 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Reading through multiple records, with Loop capability

P: 4
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.
Dec 22 '06 #1
Share this Question
Share on Google+
2 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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
Dec 25 '06 #2

NeoPa
Expert Mod 15k+
P: 31,494
Try this :
Expand|Select|Wrap|Line Numbers
  1. SELECT R.Resource,Sum(D.Quantity * R.Time) AS Load
  2. FROM Demand AS D
  3.   INNER JOIN Route AS R
  4.   ON (D.Item_ID=R.Item_ID)
  5. GROUP BY R.Resource
  6. 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.
Dec 28 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.