Calculate Time difference with a twist...

[Hfnet]

I have a database for our PC repair department, and my boss wants to see how long repairs take. Easy enough but..

1) he wants on bench repair time in hours and minutes
2) jobs can be paused and resumed and he wants this filtered out
3) We work 9 - 5.30, so if a job goes on bench at 5pm, I need to exclude out-of-hours time.

I have a table with a field for date on bench and one for time on bench

I have a table for paused jobs, with datestart and timestart, and dateend and timeend (date and time fields are all seperate in the entire database)

So if a job is set on bench on 06/07/2005 at 09:00:00 and is paused on 06/07/2005 at 09:30:00, resumed at 10:00:00 and set as repair done on 06/07/2005 at 10:30:00 I need the output time as 1 hour

I am currently using an mdb back end but am also developing in SQL Server to migrate, so the function would need to work in both.

Thanks

 

[KenReay]

Hi

You need to tel us your table structure

I assume you have a table of job ids, and linked to it in a one to many relationship, a table of times worked on each job

Yes?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Hfnet]

Yes.

Table notes holds the note "Item set on bench" and a date field and a time field, also it holds the note "Item Ready for Finshing", again with a date & time. This table links with a common "ALID" field

Table Pendings holds a field ALID and also datestart, timestart, dateend, timeend fields.

Using these fields I need to extract the time between "Item Set on Bench" and "ready for finishing", minus any paused time and of course allowing for the working week being Monday - Friday 09:00 - 17:30

The dates and times are stored in the format mm/dd/yyyy and hh:mm:ss