working hours excluding weekends

[sdel_nevo]

Hi Guys



I'm really struggling with the query I need to work out the time it has taken to close a support ticket. the ticket times are recorded in the table called "AssetNotes" with the date and time values of the ticket being held in the issueOpenedTime and Noteendtime fields.

when a ticket is raised the date and time is recorded in the "Issueopenedtime" field and when it's marked as "closed" the end time is recorded in the "Noteendtime"field

if the ticket is placed "On hold" the date and time is recorded in the "HoldStarttime" field and when it's "taken off hold" this time is also recorded in the "HoldEndtime" field


what I am really struggling with is how to workout the time a ticket has been open and only calculate the days:hours:minutes during week days during work hours of 8am to 5PM as no one here works Saturday or Sundays, also if the ticket has been put on hold I need to deduct this time from the time the ticket has been open.


I have 3 queries that I am using but at present these count the total hours including weekends, these can be set to run between 2 dates

HoldTimeByIssue

this query calculates the time that the ticket has been placed on hold

SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, NZ(Sum(DateDiff("h",[HoldstartTime],[Holdendtime])),0) AS [Hold Time], AssetNotesType.ID, AssetNotes.Closed
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, AssetNotesType.ID, AssetNotes.Closed
HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((AssetNotes.Closed)=True));

Responcetimebyissue

this query calculates the total time the ticket has been open

SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, AssetNotesType.ID, NZ(Sum(DateDiff("h",[issueOpenedTime],[NoteEndtime])),0) AS [Actual Time]
FROM AssetNotesType RIGHT JOIN AssetNotes ON AssetNotesType.ID = AssetNotes.NotesType
WHERE (((AssetNotes.IssueClosed) Between [forms].[ResponceTimeDates].[startdate] And [forms].[ResponceTimeDates].[enddate]))
GROUP BY AssetNotesType.NoteType, AssetNotesType.ID
HAVING (((AssetNotesType.NoteType) Not Like "General Note") AND ((NZ(Sum(DateDiff("h",[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));

TicketResolutiontimes

should deduct the holdtime from the responsetime

SELECT AssetNotesType.NoteType, ResponceTimeByIssue.[Actual Time], HoldTimeByIssue.[Hold Time] AS HoldTime, ([Actual Time]-[HoldTime]) AS [Total Minutes], ResponceTimeByIssue.CountOfAssetNotesID AS [Number Of Tickets], ([Total Minutes]\[Number Of Tickets]) AS [Avg Minutes], [AVG Minutes] AS [Average Hours]
FROM (AssetNotesType LEFT JOIN HoldTimeByIssue ON AssetNotesType.ID = HoldTimeByIssue.ID) RIGHT JOIN ResponceTimeByIssue ON AssetNotesType.ID = ResponceTimeByIssue.ID;


I Have attached a copy of the database in access 2007 accdb format
Database1.zip
if anyone could help that would be wonderful

Many Thanks

Steve

[SuperFrog]

If you looking to count working days between 2 dates, use following function: Public Function DateDiffW(BegDate, EndDate) Const SUNDAY = 1 Const SATURDAY = 7 BegDate = CDate(BegDate) EndDate = CDate(EndDate) Dim NumWeeks As Integer If BegDate > EndDate Then DateDiffW = 0 Else Select Case Weekday(BegDate) Case SUNDAY: BegDate = BegDate + 1 Case SATURDAY: BegDate = BegDate + 2 End Select Select Case Weekday(EndDate) Case SUNDAY: EndDate = EndDate - 2 Case SATURDAY: EndDate = EndDate - 1 End Select NumWeeks = DateDiff("ww", BegDate, EndDate) DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate) End If End Function I found it on web long time ago, and you can then multiply hours by working days. If you looking for hours itself, you can modify function to return hours. Just call it in your query by creating new column: DateDiffW(BegDate, EndDate) (change BegDate and EndDate with your time created and time closed) Hope this help, SF

[sdel_nevo]

Hi Superfrog

thanks for this, I will try to implement it into my queries

Many thanks again

Steve