Struggling with working hours query

[sdel_nevo]

Hi Guys



With help from the community on this site I have been using this query with to work out our response times by issues for our help desk support database.

SELECT Count(AssetNotes.AssetNotesID) AS CountOfAssetNotesID, AssetNotesType.NoteType, AssetNotesType.ID, NZ(Sum(DateDiff("n",[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("n",[issueOpenedTime],[NoteEndtime])),0)) Is Not Null));

the above query calculates the minutes [Actual Time] between the start and finish times this is working really well.

what I am having trouble with and to be honest totally confused by is how to calculate the time it has taken during working hours Monday - Friday 9am to 5pm.

any help with this would be fantastic

many thanks in advance

Steve

[ranman256]

Start with a query say qsWorkTime, that takes the current date/time and re-sets the time to the Workday range:

iif ([issueOpenedTime]> #9:00 am#,issueOpenedTime,#9:00 am#) as StartTime
iif ([NoteEndtime]< #5:00 Pm#,NoteEndtime,#5:00 Pm#) as EndTime


THEN use your query above on this qry qsWorkTime that only shows the time between 9-5.

[sdel_nevo]

Hi ranman256

the above you sent looks great, but how would I do that? do I create a totally new query? based on the one above or create a new query and reference this into the above query.

what would I then have to do to remove the time the ticket was open over the weekend?

if I opened a ticket on Friday at 9am and closed it Monday morning at 11am I need to get 10 as the hours run
8 hours for Friday and 2 hours on Monday total hours 10

this is above my understanding of access but this website is fantastic for learning,

many thanks again for your help

Steve

[ranman256]

I see the complication...wow

Before you start, You'd have to run a 'fix' on the time table, If start and end time cross over the weekend.
I would say a UNION query.
Q1 would do the fix I gave above, but include same day cutoffs.

iif ([issueOpenedTime]> format([issueOpenedTime],"mm/dd/yy") & " 9:00 am",[issueOpenedTime],format([issueOpenedTime],"mm/dd/yy") & " 9:00 am") as StartTime
iif ([NoteEndtime]< format([NoteEndtime],"mm/dd/yy") & " 5:00 Pm",[NoteEndtime]< format([NoteEndtime],"mm/dd/yy") & " 5:00 Pm") as EndTime

Q2 would have to fix the cross day cutoffs (like new records)
select [id], (format([NoteEndtime],"mm/dd/yy") & " 9:00 am") , [NoteEndtime]
where format([issueOpenedTime],"mm/dd/yy") < (format([NoteEndtime],"mm/dd/yy")

Q3 would be the union of 1 & 2
select * from Q1
union
select * from Q2

Now you have all records for each day broken up in business days to run your query against Q3.

[sdel_nevo]

Hi

thanks for getting back

yes q1 includes same day cutoff so that's good

thanks for the heads up I will try q2 and q3 and let you know how I get on

many thanks

Steve

[recyan]

Whenever free, try & go thro the discussion in below thread ( May be or May not be relevant to you):

http://forums.aspfree.com/microsoft-...ys-379275.html

Thanks

[sdel_nevo]

Hi recyan

I will take a look at that, many thanks
Steve