subtracting out data from a date range

[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.

 

[[MVP] S.Clark]

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.

 

[Guest]

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?