On Wed, 2005-06-01 at 19:59 +1000, Grant Parnell - EverythingLinux wrote: > I'm trying to do some metrics on how long it takes to process an order in > our system based on time between printing the picking slip and bagging the > goods for dispatch. The aim is to determin the maximum performance if we > were to have one guy do nothing but pick & pack orders.
> +-------+---------------------+---------------------+----------+ > | ordno | pickslip_printed | docketdate | packmins | > +-------+---------------------+---------------------+----------+ > | ordno | 2005-06-01 14:32:16 | 2005-06-01 14:34:47 | 3 | So the issue is cases like this one where three orders are processed in parallel: > | ordno | 2005-06-01 15:12:27 | 2005-06-01 15:27:26 | 15 | > | ordno | 2005-06-01 15:12:28 | 2005-06-01 15:30:25 | 18 | > | ordno | 2005-06-01 15:12:29 | 2005-06-01 15:21:53 | 9 | So, if we want to consider the time each order actually takes to process, and we're allowing parallel packing we need to make some assumptions about what's going on with that parallel processing. Let's make this assumption: in overlapped time periods, it is acceptable to use the docketdate to demarcate time spent on different orders. So the analysis you're looking for then is not how much time can directly be attributed to each order, but rather how much time is spent on each order, without necessarily knowing which order took that time (you don't have enough data there to do direct attributions even if you wanted to). If we can assume that then you can make some headway on the problem -- you can flatten your data-structure to a time-line of events and measure the time between events. Further, you can also ask the database to ignore time when no order processing is occurring. (excuse my SQL here, it's been a few months) select eventdate, eventname, eventdiff from (select pickslip_printed, 'printed', 1 from orders) union all (select docketdate, 'docketed', -1 from orders) order by eventdate asc; Which discards the relationship of times to specific orders, and allows analysis of just the time elapsed between events. In addition, the eventdiff column will allow you to work out how many orders are outstanding at any point in the timeline. I'd make that into a view, and then build another view that evaluates the sum of the eventdiff column up to that row for every row. Whereever that sum is > 0, there are orders being worked on. Once you get to that point, the elapsed time on an order will be the difference between some of the adjacent rows, which is similarly easily calculated. I say "some of", because of course the one second that elapsed between those orders being "printed" does not represent packing time. If you think about it, you can solve that problem but that gets a bit fiddly (you'd need to differentiate between print-print, print-docket and docket-docket scenarios. The latter two would represent order processing time, which is doable, but you need to keep inter-row state, which is slightly tricky in SQL), so you're probably better off employing some kind of heuristic given what you know about the data (eg that you don't believe an order could be packed in < 30 seconds or similar). Not bullet-proof, but it's a start. HTH, James. (recovering SQL abuser) -- My love burns for you A Thermonuclear rose Now lets go make out -- http://www.solardeathray.com/rose.html
signature.asc
Description: This is a digitally signed message part
-- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html