combining 2 queries

[sdel_nevo]

Hi Guys



I have two queries that I would like to add together, the first Query "AverageResponseTimeByIssue" records the average time it takes to resolve a Support Ticket based on category "NoteType" if "closed" is set to true


The second query "AverageHoldTimeByIssue" works out the average time that tickets have been placed on hold based on category "NoteType" if "Onhold" is set to true

what I am trying to do is subtract the on hold time from the response time but I can't get my head around how to do it,

what I am looking for is the averageResponcetimeByIssue - AverageHoldTimeByIssue but still grouped on Notetype.

AverageHoldtimeByissue sql

SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, NZ(Avg(DateDiff("n",[HoldStartTime],[HoldEndtime])),0) AS AverageTime
FROM AssetNotesType INNER JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType
WHERE (((AssetNotes.IssueClosed) Between [forms].[ResponceTimeDates].[startdate] And [forms].[ResponceTimeDates].[enddate]))
GROUP BY AssetNotesType.NoteType, AssetNotes.[On Hold]
HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((AssetNotes.[On Hold])=True));

AverageResponseTimeByIssue SQL

SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, NZ(Avg(DateDiff("n",[issueOpenedTime],[NoteEndtime])),0) AS AverageTime
FROM AverageHoldTimeByIssue, AssetNotesType INNER JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType
WHERE (((AssetNotes.IssueClosed) Between [forms].[ResponceTimeDates].[startdate] And [forms].[ResponceTimeDates].[enddate]))
GROUP BY AssetNotesType.NoteType
HAVING (((AssetNotesType.NoteType) Not Like "General Note"));

any help would be fantastic

Steve

[June7]

Is NoteType the issues?

I don't suppose both queries have all the same NoteType values?

I do suppose you have a table of all note types?

Join the two aggregate queries to the NoteTypes table.

You might first want to rename the two AverageTime fields different so the new query doesn't have two fields with same name.

[sdel_nevo]

Hi June

Thanks for the heads up, joined the queries to the table works really well

many thanks

steve