Average item count per hour query calculation (1 Viewer)

bruceblack · Mar 30, 2018

Hi everyone! Would kindly like to ask someone for help here.
Since im terrible at these type of things :S.

I have a query with 4 columns:

Items, user, starttime, finishtime

I would like to calculate: how many items, each user has done, on everage, per hour.

In this same query i already do a "count" of each user to get a total item count.
(which i also need).

Can somebody tell me what the best approach is here? I dont understand anymore and im stuck on it for quiet a while. Thanks in advance!!!

Ranman256 · Mar 30, 2018

Q1, put the data in HR blocks,
Select person ,count( work) as CtPerHr, hour(timeField), format(timeField,"Mm/dd/yyyy") as Day from table

Q2, get Total of Q1
Select person, sum(CtPerHr) as TotPerDay Day from Q1

Q3, get avg,from both...
Select Q1.person, q1.ctPerHr/q2.TotPerDay as AvgWk, Day from Q1,Q2 where Q1.Person= Q2.person

Ranman256 · Mar 30, 2018

Q1, put the data in Hour blocks,
Select person ,count( work) as CtPerHr, hour(timeField), format(timeField,"Mm/dd/yyyy") as Day from table

Q2, get Total of Q1
Select person, sum(CtPerHr) as TotPerDay Day from Q1

Q3, get avg,from both...
Select Q1.person,q1.hr, q1.ctPerHr/q2.TotPerDay as AvgWk, Day from Q1,Q2 where Q1.Person= Q2.person

arnelgp · Mar 30, 2018

Errh...
Can you give more detail and sample of table you have maybe in excel or access.

bruceblack · Mar 31, 2018

Damn. Im sorry. I don't really understand what you mean.
Thanks for the swift reply though.

I would like to calculate: how many items, each user has done, on everage, per hour.

items column are my record ID's. So they are unique
users column are just the names of people who created these ID's
My start en finish time columns are formatted like "7:15:36 AM"

Here is a screenshot of my results and design:

https://ibb.co/mPu1AS

jdraw · Mar 31, 2018

Bruce,

I mocked up some data in a table called tBruce
A few records for Maggie and Dunama froom your image, and a few records for Bruce and Andy

Code:

id	itemID	userid	starttime	finishtime
1	i-003741	Maggie	10:59:02 AM	11:06:55 AM
2	i-003742	Maggie	11:07:00 AM	11:07:04 AM
3	i-003743	Maggie	11:07:09 AM	11:07:11 AM
4	i-003757	Dunama	2:55:12 PM	2:58:03 PM
5	i-003758	Dunama	2:58:21 PM	3:01:34 PM
6	i-888888	Bruce	2:59:00 PM	3:00:00 PM
7	i-777777	Andy	3:00:00 PM	3:02:00 PM
8	i_777778	Andy	3:03:00 PM	3:04:00 PM

(Bruce 1 record) value was 1 minute (60 sec) and
Andy 2 records ( values 2 min and 1 min) with avg (90 sec) to simplify calculation.

My approach was find avg time(in seconds) to build 1 item. Then use that value to calculate number of items per hour(1 hour = 3600 seconds). Not exactly what you asked, but should be an approximation (I think). This is saying, based on their avgTiime to build an Item, here is how many items each would/could build in 1 hour.

The query SQL

Code:

SELECT Count(tBruce.itemID) AS CountOfitemID
, tBruce.userid
, Round(Avg(DateDiff("s",[tBruce]![starttime],[tBruce]![finishtime])),3) AS AvgBuildTimeSec
, 3600/[avgBuildTimeSec] AS AvgNumPerHour
FROM tBruce
GROUP BY tBruce.userid;

Result using tBruce data

Code:

CountOfitemID	userid	AvgBuildTimeSec	AvgNumPerHour
2	Andy	90	40
1	Bruce	60	60
2	Dunama	182	19.7802197802198
3	Maggie	159.667	22.546925789299

Hope it's helpful.

bruceblack · Mar 31, 2018

Did read it jdraw

. Again thanks for backing me up.
When it comes to creativity i can help others, when it comes to logic and statements like here, my brain gets stuck on the stupid dial :banghead:
(its beyond me)

Still stuck. I'll get back when i figured your solution.

Average item count per hour query calculation (1 Viewer)

bruceblack

Registered User.

Ranman256

Well-known member

Ranman256

Well-known member

arnelgp

..forever waiting... waiting for jellybean!

bruceblack

Registered User.

jdraw

Super Moderator

bruceblack

Registered User.

Similar threads

Users who are viewing this thread