On Tue, Dec 2, 2008 at 12:28 PM, MB Software Solutions General Account <[EMAIL PROTECTED]> wrote: > Stephen Russell wrote: >> On Tue, Dec 2, 2008 at 11:06 AM, MB Software Solutions General Account >> <[EMAIL PROTECTED]> wrote: >>> Background: >>> * MySQL5 - MyISAM tables >>> * Customer is a pallet creation company--they manufacture pallets and >>> then treat the pallet (heat/dip/other) before shipping to customer. >>> * 3 tables in focus for this: >>> -- ORDERS: actual pallet orders >>> -- QUEUE: orders sent to the treatment area -- this is a MANY child >>> for the ORDERS table >>> -- QTY_IN_QUEUE: it's basically the SUM on the QUEUE.QTYs for each >>> order so that I don't have to always do a SUM of the QUEUE table to know >>> the total quantity sent to the treatment area. This allows me to a have >>> a simple 1:1 INNER JOIN query that quickly returns the sum qty from the >>> ORDERS table >> ----------------------------------------------------------- >> >> You only need 2 tables for data and one for a lookup. >> >> Select Sum(q.*), q.TypeID, pt.Description >> from Orders Ord >> left join QUEUE q >> on ord.OrderNumber = q.OrderNumber >> left join ProcessTypes pt >> on pt.ID = q.PTID >> where ord.shippedDate is Null >> group by q.TypeID, pt.Description >> >> You may have a flag set in Queue that defines its been processed so >> use that instead and drop the entire use of Orders table. >> > > You don't understand...I know I could do the SUM on the LEFT JOIN > query---I don't want to do that because this query is being fired for > SEVERAL orders at a time. Picture the schedule grid as this: there are > 4 nailing machines on the lefthand (vertical) axis, and 5 dates across > the top, with each nailer/date coordinate holding up to 4 orders. > Here's a screenshot: http://mbsoftwaresolutions.com/images/scheduler.png --------------------------------------------------
What I see in that GUI is not the order(s) but the Queue table. Right? Or is that a join to Orders that show B.Braun as the customer as well as the equipment table for the process as well?. > Each of those "cells" needs to know the quantity sent to the treatment > queue. If the total quantity has been sent, then the border around the > cell changes to a gray or yellow, depending on whether it's done being > treated or not. At that point, the production supervisor can take these > "virtual magnets" off the "board" by right-clicking the "cell." It's a > much easier management process than the old manual ways they used to do > with a big physical magnet board where they'd use erasable marker to > list details of each order. ------------------------- Does your Queue table have a status column? Used to represent what station the material is at? Or will you have to portion out 560 ordered, 280 nailed and 280 treated? > I don't want to tap that query for a LEFT JOIN using the aggrevate SUM > function. I'd rather just hit the QTY_IN_QUEUE table for a single 1:1 > match. I did that for performance, as the process before wasn't as > quick as I'd like it to be. Think of a SEEK instead of processing a SUM > SQL. ;-) ----------------- I see that you only need to do the query one time and include more group by data to give the granularity you need. Depending on how much material volume you have, do you need to update every min. or every 3? This query should only take a second or two to run, it should be all in ram if your running this frequently. -- Stephen Russell Sr. Production Systems Programmer Mimeo.com Memphis TN 901.246-0159 _______________________________________________ Post Messages to: [email protected] Subscription Maintenance: http://leafe.com/mailman/listinfo/profox OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech Searchable Archive: http://leafe.com/archives/search/profox This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED] ** All postings, unless explicitly stated otherwise, are the opinions of the author, and do not constitute legal or medical advice. This statement is added to the messages for those lawyers who are too stupid to see the obvious.
