Datediff & CASE question 1

[Michael091170]

I need to run a query on my database that measures the difference between two datetime values. Submitted and Resolved.

This is reasonably simple enough with a datediff function. The problem comes as have I factor in core working hours and week days.

Our core hours are Monday - Friday 9:00 - 17:00 so for example:

If a call is logged at 16:45 on Friday and resolved on 09:10 the following Monday the real time to resolution is 25mins. Datediff however includes the weekend and non core hours.

How would you apply these conditions in an expression.

If anyone's done something similar before I'd be interested to find out how you achieved it.

Thanks

 

[AlexCuse]

Michael - take a look at this thread, for getting weekdays between two dates.

If you can identify the weekdays in between, then you can just count each of them as 8 hours. Then add the difference between start time and office closing time on the first day, and difference between office opening time and completion time on the last day.

Am I making sense?

Hope this gets you started, post back with any specific questions.

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

post back with any specific questions

Which thread would that be then Alex?


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[ilyad]

May be this one

http://tek-tips.com/viewthread.cfm?qid=857523?

Or a recent SQL teaser thread?

 

[AlexCuse]

thread183-1358690

Ignorance of certain subjects is a great part of wisdom