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
