Success rate on collection times

adrianscotter

Registered old fart!
Local time
Today, 23:12
Joined
Jul 7, 2014
Messages
124
Hey guys

I'm stuck and have been for several hours now trying to get this to work :banghead: I need to get a percentage figure for the success rate of collections. Certain items must be collected within a time window that can be from 1 hour to 8 hours. For a monthly report, they would like to know what percentage of bags were collected in the time window allocated. I have a date range (the report is by month). The bag collection data stored is 'From Time' and 'To Time' plus the 'Actual Time' collected. At the moment, I must have a dozen queries and a 'Google history' that has about 400 entries trying to nail this but I have had no success. Anyone give me a pointer before I crack open a bottle of scotch :rolleyes:
 
You might need a couple of queries to break it down but Air code something like

First query count all the records by month
Second query count the same records but this time only where Actual Time collected is NOT between from time and to time. This gives you the number of "missed" collections.
Percentage is second no per month divided by first query number per month.
 
Thanks Minty, I'll give it a go and let you know :)
 
would have thought something like a aggregate function would work. Something like

Code:
SELECT month(FromTime) as CollectionMonth, abs(sum(ActualTime Between FromTime and ToTime))/count(FromTime) as CollectedInWindow
FROM myTable
GROUP BY month(FromTime)

this assumes your times are actually stored as datetime, not just time. This will produce a decimal e.g. 0.85 which can either multiplied by 100 or formated as a percentage depending on what you are doing with the data
 
Separate dates and times I'm afraid CJ but thanks for your time...
 
Separate dates and times I'm afraid
add them together

FromDate+FromTime etc

or if all times are always within the same day you just change the month(FromTime) to month(FromDate)
 
OK, with some playing, this is what I ended up with...

5 x queries, one to total the number of jobs in a month, one to total the number of missed jobs per month, one each to total those figures and one to work out the percentage missed. I didn't realise but some of the collections have multiple bags, some have multiple locations so I couldn't total the collection jobs in the initial queries. What a ball ache! Many thanks to Minty and CJ (I've put the whiskey bottle back in the cupboard)
 

Users who are viewing this thread

Back
Top Bottom