Query difference between two date/time fields (with a twist)

  • Thread starter Thread starter tintin
  • Start date Start date
T

tintin

I have two fields in a seperate tables; 1st field is a "time received"
box with date and time. Second field is for "time despatched" with the
same format and I want to calculate the difference between the two i.e.
"response time". The problem is that the "response time" has to be
based on a working day of 08.30 to 17.30 so that the difference between
day 1 17:00 and day 2 09:30 would be 1.5 hours.
Any ideas on how to define this query?
 
Assuming the time is formated as a time how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived, (17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched - DayReceived -1)
* 9))

Kelvin
 
This will work as long as the item was received and dispatched during
the same week, but will generate erroneous results if the item is held
over a weekend or holiday.

--
HTH

Dale Fye


Assuming the time is formated as a time how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived,
(17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched -
DayReceived -1)
* 9))

Kelvin
 
OK, how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived, (17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched - DayReceived -1)
* 9) -
(cint(datepart("ww",DayDispatched))-cint(datepart("ww",DayReceived))*2*9 )

This should remove the 2 days for any weekend. Holidays will still be a
problem. It will take more than a simple query to resolve that situation.

Kelvin

Dale Fye said:
This will work as long as the item was received and dispatched during
the same week, but will generate erroneous results if the item is held
over a weekend or holiday.

--
HTH

Dale Fye


Assuming the time is formated as a time how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived,
(17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched -
DayReceived -1)
* 9))

Kelvin
Click to expand...
 
That will definately take care of the weekends. Since he didn't
explain whether he was concerned with weekends, I just wanted him to
understand that it would not work over the weekends. Don't get me
wrong, I thought your initial solution was pretty good, and
significantly shorter than where I was going with my attempt.

Another option might be to use a WorkDays function to return the
number of days between the DayReceived and DayDispatched. I've got
one, but it uses a holidays table and another table I have, and I
didn't want to go into the detail necessary to explain it to a newbie.
--
HTH

Dale Fye


OK, how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived,
(17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched -
DayReceived -1)
* 9) -
(cint(datepart("ww",DayDispatched))-cint(datepart("ww",DayReceived))*2
*9 )

This should remove the 2 days for any weekend. Holidays will still be
a
problem. It will take more than a simple query to resolve that
situation.

Kelvin

Dale Fye said:
This will work as long as the item was received and dispatched during
the same week, but will generate erroneous results if the item is held
over a weekend or holiday.

--
HTH

Dale Fye


Assuming the time is formated as a time how about

=IIF(DayDispatched = DayReceived, TimeDispatched - TimeReceived,
(17.50 -
TimeReceived) + (TimeDispatched - 8.50) + ((DayDispatched -
DayReceived -1)
* 9))

Kelvin
Click to expand...
 
Yes, I will have to take weekends into account but not holidays. I
haven't had time to try your solutions, but I will post once I have.
Many thanks for your help.
 
Sorry guys. I should have said that my date & time received details are
in a single field formatted as a general date. I guess this means that
I should rewrite the tables to have seperate date and time boxes in
order to calculate the day difference and thus, response time. Is this
so, or is there an easier way? Thanks for your perseverence with a
newbie.
p.s. any tips on where I should begin learning this SQL stuff?
 
Back
Top