subtracting out data from a date range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a query that is running to capture a delay to close time frame.
Currently I am subtracting the open datestamp from the closedatestamp in a
query. I need to find a way to reflect that in a business hours scenario. I
have a scenario that we are open monday - friday from 6:00 am to 6:00 pm

For Example instead of an interaction that was opened Friday at 5:30 pm and
closed Monday at 6:30 am, where now the query is giving me close - open =
11/8/04 06:30 am - 11/5/04 05:30 pm = 61:00:00 (hh:mm:ss). what I would like
to have the query give me is close - open = 11/8/04 06:30 am - 11/5/04 05:30
pm = 1:00:00 (hh:mm:ss).

or in a worst case I would like to atleast take out the weekend or holiday
out of the scenerios.

Thank you in advance for the assistance.
 
If you allow storage of records to have multiple start and end times, you
may be able to solve easily. So, your example would have:

Record 1
Start: Friday 17:30
End: Friday 18:00
Duration = 00:30

Record 2
Start: Monday 06:00
End: Monday 06:30
Duration = 00:30

Then, you could sum the two durations, 30mins + 30mins = 1 hr.
 
Unfortunatly the records do not stop or close at the end of a business day so
I cannot just add the duration per day from each period. is there a way that
I could identify a datepart as a 1 through 7 and tally the duration per day
to either subtract or add each period together?
 
Back
Top