Basically, I need to select items from todays’ schedule (named query to select from DB including item#, Qty needed, batch wt ), lookup which components make up each item (select statement from DB may return up to 8 rows of component/percentage per item), calculate sum of each component needed to complete the schedule and display it in a table neatly and clearly.
I think I will have to do this through a script somehow but I am not sure how to handle each sub dataset returned from looking up each individual item on the schedule. Beyond that I’m not exactly sure how to make the necessary calculations with that data. Any help or suggestions as to a proper approach would be greatly appreciated!!
There’s many ways you could do this in a script, but I think the easiest route would be to use one query, join the two tables, use “GROUP BY”, and aggregate using “SUM()” on whatever columns.
Because of how these tables were designed, I do not think I can combine these queries like you mentioned. For each Item on the schedule I may have up to 8 components so 8 rows returned per item on the schedule and any of these components may be present in multiple items. When looking for the components I also have to ensure that they have the most recent time and date stamp and the amount of rows returned may vary.
Photos of the schedule and component tables attached.
The second part of what I need to accomplish is to compare the totals of each component needed for a days’ schedule with our inventory and notify the scheduler if we do not have the components needed to complete. Ideally, when ran, this would also subtract the amount of batches already completed from the batches needed.
