Average time and grouping

[sdel_nevo]

Hi guys



I am really sorry if this has been answered before but I am having a problem with a query.

i have a query that returns the starttime and endtime from a table called "assetNotes" based on "Notetype" and works out the average time

this is working really well

This is the SQL behind the Query


SELECT DISTINCTROW AssetNotesType.NoteType, AssetNotes.Closed, AssetNotes.IssueOpenedTime AS StartTime, AssetNotes.NoteEndTime AS EndTime, Format([Starttime]-1-[Endtime],"Short Time") AS AverageTime
FROM AssetNotesType INNER JOIN AssetNotes ON AssetNotesType.ID=AssetNotes.NotesType
GROUP BY AssetNotesType.NoteType, AssetNotes.Closed, AssetNotes.IssueOpenedTime, AssetNotes.NoteEndTime
HAVING (((AssetNotes.Closed)=True)) OR (((AssetNotes.NoteEndTime) Is Not Null))
ORDER BY AssetNotes.NoteEndTime DESC;

This is the result of the query

AverageResponceTimesPerIssuesType
NoteType	Closed	StartTime	EndTime	AverageTime
Training - Software Based	Yes	22/07/2014 10:49:38	22/07/2014 10:50:51	00:01
Swan - Issues	Yes	21/07/2014 15:22:04	22/07/2014 09:55:39	18:33
E-Mail - Issue	Yes	21/07/2014 16:59:08	22/07/2014 09:23:01	16:23
E-Mail - Issue	Yes	21/07/2014 16:48:54	21/07/2014 16:49:35	00:00
Software - Issue	Yes	21/07/2014 16:32:15	21/07/2014 16:33:29	00:01
Planet Software - Issue	Yes	18/07/2014 08:26:00	21/07/2014 15:15:13	06:49
PC Hardware - Issue	Yes	15/07/2014 13:14:53	21/07/2014 15:04:25	01:49
Lisa - Issues	Yes	21/07/2014 14:59:03	21/07/2014 14:59:21	00:00
Swan - Issues	Yes	21/07/2014 13:30:26	21/07/2014 14:40:58	01:10
Training - Software Based	Yes	21/07/2014 14:26:31	21/07/2014 14:27:21	00:00
Software - Issue	Yes	21/07/2014 13:30:52	21/07/2014 14:24:37	00:53
Training - Software Based	Yes	21/07/2014 13:50:14	21/07/2014 13:51:12	00:00
Software - Issue	Yes	21/07/2014 13:30:25	21/07/2014 13:49:40	00:19
Networking - Issue	Yes	21/07/2014 13:05:22	21/07/2014 13:12:05	00:06
Software - Issue	Yes	21/07/2014 11:52:07	21/07/2014 12:37:10	00:45
E-Mail - Issue	Yes	21/07/2014 10:12:13	21/07/2014 11:34:31	01:22
General Note	Yes	21/07/2014 10:34:41	21/07/2014 11:32:12	00:57
Training - Software Based	Yes	21/07/2014 11:22:03	21/07/2014 11:22:53	00:00
PC Hardware - Issue	Yes	21/07/2014 10:11:39	21/07/2014 10:12:00	00:00
E-Mail - Issue	Yes	21/07/2014 10:10:54	21/07/2014 10:11:20	00:00
Training - Software Based	Yes	21/07/2014 09:21:50	21/07/2014 09:22:58	00:01
Backup - Issue	Yes	18/07/2014 11:31:12	21/07/2014 08:59:23	21:28
E-Mail - Issue	Yes	18/07/2014 16:54:43	18/07/2014 16:55:16	00:00
General Note	Yes	18/07/2014 16:32:40	18/07/2014 16:34:58	00:02
Scanner - Issue	Yes	18/07/2014 16:15:25	18/07/2014 16:28:50	00:13

What i would really like to do but can’t is group the notetype together and calculate the average time so for example
Notetype Closed Averagetime
E-Mail issue yes 23:20
Software – issue yes 05:10

Anyhelp would be most welcome.

Steve

[orange]

I used your data as a sample, created a table ( "Times").

ID	NoteType	Closed	Starttime	Endtime
1	Training - Software Based	Yes	22/07/2014 10:49:38 AM	22/07/2014 10:50:51 AM
2	Swan - Issues	Yes	21/07/2014 3:22:04 PM	22/07/2014 9:55:39 AM
3	E-Mail - Issue	Yes	21/07/2014 4:59:08 PM	22/07/2014 9:23:01 AM
4	E-Mail - Issue	Yes	21/07/2014 4:48:54 PM	21/07/2014 4:49:35 PM
5	Software - Issue	Yes	21/07/2014 4:32:15 PM	21/07/2014 4:33:29 PM
6	Planet Software - Issue	Yes	18/07/2014 8:26:00 AM	21/07/2014 3:15:13 PM
7	PC Hardware - Issue	Yes	15/07/2014 1:14:53 PM	21/07/2014 3:04:25 PM
8	Lisa - Issues	Yes	21/07/2014 2:59:03 PM	21/07/2014 2:59:21 PM
9	Swan - Issues	Yes	21/07/2014 1:30:26 PM	21/07/2014 2:40:58 PM
10	Training - Software Based	Yes	21/07/2014 2:26:31 PM	21/07/2014 2:27:21 PM
11	Software - Issue	Yes	21/07/2014 1:30:52 PM	21/07/2014 2:24:37 PM
12	Training - Software Based	Yes	21/07/2014 1:50:14 PM	21/07/2014 1:51:12 PM
13	Software - Issue	Yes	21/07/2014 1:30:25 PM	21/07/2014 1:49:40 PM
14	Networking - Issue	Yes	21/07/2014 1:05:22 PM	21/07/2014 1:12:05 PM
15	Software - Issue	Yes	21/07/2014 11:52:07 AM	21/07/2014 12:37:10 PM
16	E-Mail - Issue	Yes	21/07/2014 10:12:13 AM	21/07/2014 11:34:31 AM
17	General Note	Yes	21/07/2014 10:34:41 AM	21/07/2014 11:32:12 AM
18	Training - Software Based	Yes	21/07/2014 11:22:03 AM	21/07/2014 11:22:53 AM
19	PC Hardware - Issue	Yes	21/07/2014 10:11:39 AM	21/07/2014 10:12:00 AM
20	E-Mail - Issue	Yes	21/07/2014 10:10:54 AM	21/07/2014 10:11:20 AM
21	Training - Software Based	Yes	21/07/2014 9:21:50 AM	21/07/2014 9:22:58 AM
22	Backup - Issue	Yes	18/07/2014 11:31:12 AM	21/07/2014 8:59:23 AM
23	E-Mail - Issue	Yes	18/07/2014 4:54:43 PM	18/07/2014 4:55:16 PM
24	General Note	Yes	18/07/2014 4:32:40 PM	18/07/2014 4:34:58 PM
25	Scanner - Issue	Yes	18/07/2014 4:15:25 PM	18/07/2014 4:28:50 PM

Then created this query:

SELECT Times.NoteType
, Times.Closed
, Avg(DateDiff("s",[starttime],[endtime])) AS AverageProcessingTimeInSeconds
FROM Times
GROUP BY Times.NoteType, Times.Closed;

This is result:

NoteType	Closed	AverageProcessingTimeInSeconds
Backup - Issue	Yes	250091
E-Mail - Issue	Yes	12814.2
General Note	Yes	1794.5
Lisa - Issues	Yes	18
Networking - Issue	Yes	403
PC Hardware - Issue	Yes	262496.5
Planet Software - Issue	Yes	283753
Scanner - Issue	Yes	805
Software - Issue	Yes	1789.25
Swan - Issues	Yes	35523.5
Training - Software Based	Yes	59.8

[ItsMe]

What happens when you try to Add Grouping and select Note Type?

[sdel_nevo]

Hi VIp and itsme

I have recreated the query based on the above and this is the output i get when i add the grouping to notetype?

Copy Of AverageResponceTimesPerIssuesType
NotesType	Closed	AverageTime
Virus - Issue	Yes	1814400
E-Mail - Issue	Yes	1814400
Lisa - Issues	Yes	1814400
Networking - Issue	Yes	1814400
Software - Issue	Yes	1814400
PC Hardware - Issue	Yes	1814400
Scanner - Issue	Yes	1814400
PDA Software - Issue	Yes	1814400
Tablet - Issue	Yes	1814400
Planet Software - Issue	Yes	1814400
Printer - Issue	Yes	1814400
TMS Clocking In / Out - Issue	Yes	1814400
Windows Software - Issues	Yes	1814400
General Note	Yes	1814400
Backup - Issue	Yes	1814400
Swan - Issues	Yes	1814400
Training - Software Based	Yes	1814400

All the average times are the same
any ideas

Steve

[sdel_nevo]

Hi Guys

i have sorted the problem many thanks for you help i had not changed Avg(DateDiff("s",[starttime],[endtime])) AS AverageProcessingTimeInSeconds
to match my table names

Avg(DateDiff("s",[issueopenTime],[Noteendtime])) AS AverageProcessingTimeInSeconds

now i feel really silly

Many Many Thanks

Steve