My god... there's some good ideas there. For now I opted for processing outside of SQL by basically breaking the day into 1 minute time slots and looking at how many orders were currently being processed in each time slot. Next I iterated again and counted up the minutes for each order such that for each real minute the attributed time was t/n where n was the number of current orders being processed. Then I simply averaged the attributed times for each order. I can get away with this approach because the data set is pretty small and it's only twice through the data.
Essentially we agree on only looking at times of the day where packing activity is occurring. The jury is out on whether you can assume the orders were processed sequentially based on docketdate. We'll never know, based on this data, how long each specific order took but then I don't care - baring exceptional cases (eg 2 hours). Still, I'll try implimenting your approach for comparison's sake. It'll certainly be interesting. On Thu, 2 Jun 2005, James Gregory wrote: > 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) > > -- ---<GRiP>--- Grant Parnell - SLUG President EverythingLinux services - the consultant's backup & tech support. Web: http://www.elx.com.au/support.php We're also busybits.com.au and linuxhelp.com.au and everythinglinux.com.au. Phone 02 8756 3522 to book service or discuss your needs or email us at paidsupport at elx.com.au ELX or its employees participate in the following:- OSIA (Open Source Industry Australia) - http://www.osia.net.au AUUG (Australian Unix Users Group) - http://www.auug.org.au SLUG (Sydney Linux Users Group) - http://www.slug.org.au LA (Linux Australia) - http://www.linux.org.au -- SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/ Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html
