Hello,
I need help programming the following.
I have a table that contains my product demand, which looks like this.
This is saying 20,000 is due by 4th week of July then 20,000 is due by second week of August then 115,000 by third week of August and so on.
I have another table which contains a set of production rules dictating when a particular item is being typically produced with in the production cycle.
Item_ID Rule ITEM#1 1 ITEM#1 4 ITEM#2 1 ITEM#3 3
This is saying, item#1 is being produced on week 1 and week 4 of each month, item#2 is being produced on week 1 and item#3 on week 3.
What I need to do is assign each line record the week and month numbers each must be produced based on the date it is due by, and the rule or rules that apply to each item so it is produced on time.
The end result should look like this
Examples:
If an item is due on week 2 of Sept and we produce it on week 3 of each month, I cannot produce it on week 3 of Sept or it will be late so I need to assign to these records (and all of the same before that date) the production month of August and the week of production 3. The program should select all records with a due dates from prior to the end of week 2 of Sept, assign the production month and week numbers and loop to the end of the table (I get 6 months worth of revolving records).
If today is July 24th and an item is being produced on week1 of each month, I need to produce on Aug. week1, everything past due and due to end of week1 of Sept.
It would easily be doable with a select query if only 1 rule applied to each item but some items have 2 rules because we produce them twice a month and I do not have the skills to program that.
Some items will not have a rule, these should be assigned the same month # they are due on but the prior week #.
For example if Item#5 is not in the rule table and 10,000 are due 7/10 (past due in that case), plus 10,000 on 8/10 and 10,000 on 9/10, then 15,000 on 10/12, the program should assign the 7/10 + 8/10 lines a production month of 08 and production week of 2 (so it is on time 8/10 which is week3), then should assign the 10/12 record a production date of October week 2 (the week of 10/05).
Months that have 5 or 6 weeks are treated as following. It is the case for August 8/31 which would be week 6.
Week 5 becomes 4 of the current month and week 6 becomes week 1 of the following month.
This is already calculated as such in the table that contains the demand orders, you can see above that the 8/31 records has been assigned a due month of 09 and due week# 1 thus is being manufactured on week4 of August to be on time. Aug 24 and 28 are week 5 and have been assigned week 4 so each month is divided in 4 quarters to coincide with the rules 1 to 4.
To make matters more complex, if an item has 2 cycles within the month, we won't produce it within the previous cycle. So in the example if ITEM#1 that is produced week 1 and week 4, I do not need to produce week 1 of the following month into the previous week 4 cycle.
On the other hand if item#1 was only produced once a month on week1, then I would produce in August week 1 all due dates from past due to the end of week September week 1. Then September 1 week production cycle I would produce due dates of Sept week 2 to 4 (and any past due) plus October week 1 (disregard the extra attached images which had errors in that matter).
TIA.