Success rate on collection times (1 Viewer)

adrianscotter

adrianscotter

Registered old fart!
Local time
Today, 06:07
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:
 
Minty

Minty

AWF VIP
Local time
Today, 06:07
Joined
Jul 26, 2013
Messages
10,371
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.
 
adrianscotter

adrianscotter

Registered old fart!
Local time
Today, 06:07
Joined
Jul 7, 2014
Messages
124
Thanks Minty, I'll give it a go and let you know :)
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Feb 19, 2013
Messages
16,610
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
 
adrianscotter

adrianscotter

Registered old fart!
Local time
Today, 06:07
Joined
Jul 7, 2014
Messages
124
Separate dates and times I'm afraid CJ but thanks for your time...
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 06:07
Joined
Feb 19, 2013
Messages
16,610
Separate dates and times I'm afraid
Click to expand...
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)
 
adrianscotter

adrianscotter

Registered old fart!
Local time
Today, 06:07
Joined
Jul 7, 2014
Messages
124
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)
 
You must log in or register to reply here.

Similar threads

A
Query to Calculate percentages of specific records in a table
Replies
5
Views
686
mike60smart
mike60smart
A
Query for Pass/Fail/Totals
Replies
5
Views
187
ashleymac
A
P
Solved IE.Quit Takes Quite Long Time to Execute
Replies
15
Views
433
Pac-Man
P
T
Cannot Sum a Calculated Control in the Report Footer
Replies
11
Views
340
The_Doc_Man
The_Doc_Man
B
Query to group on Day with sum total on a field & filter on Time but NOT group on the time
Replies
8
Views
477
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom