Advanced Datediff?

I have two fields, columns A and B
Both are datetime

A contains date / time a call is logged. B contains the time the call was resolved.

For SLA checking, I need to know how many hours difference there is between them, quite simple so far.

The problem is, the calculation needs to take into account the working day. i.e. 9-5 for example.

So, if a call is logged at 4pm then there is only 1 hour of the SLA time used for the current day and the clock so to speak carrys on again from 9am the next day. Meaning the engineer has until 11am the following day. Thats what I need to show.

Anyone able to help?
LVL 1
andy_boothAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mcmonapCommented:
Hi andy_booth,

How about something along the lines below.  This just checks whether the call was open for more than one day, if that is the case then the number of hours is calculated less 16 (number of hours between 5pm and 9am) multiplied by the number of days.

--start code
DECLARE @tblDates TABLE (logged datetime, resolved  datetime)
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,95,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,1395,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,2795,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,2995,getdate()))

SELECT
      logged
      , resolved
      , CASE
            WHEN DATEDIFF(dd, logged, resolved) > 0 THEN DATEDIFF(hh, logged, resolved) - (DATEDIFF(dd, logged, resolved) * 16)
            ELSE DATEDIFF(hh, logged, resolved)
      END WorkingHoursToResolve
FROM
      @tblDates
--end code
0
andy_boothAuthor Commented:
Thanks mcmonap

That looks like it would kind of work. Except, what would happen on a Friday, Saturday and Sunday are non working days.

Sorry, maybe I should have said that, assumptions are evil :)

Also, could it be adpated slightly to work on two columns?
For those that have a resolved date, for it to work as is i.e. "Resolved in", for those that the resolved date is empty, for it to work out time remaining.

I will increase the points due to my scope creep.
0
LowfatspreadCommented:
so you've got the working shift problem,
and the public holiday problem

i'm surprised that you haven't got the "Call on Hold" situation and
multiple people working on call calculations as well ...

presumably a call can be raised and resolved out of the chargeable hours as well....


0
andy_boothAuthor Commented:
For the time being, I would be happy with it working with just 9-5 Mon - Fri.

0
mcmonapCommented:
There are probably better ways to do this, but it seems to work:

DECLARE @tblDates TABLE (logged datetime, resolved  datetime)
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,95,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,1395,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,2795,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,2995,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(dd,5,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(dd,9,getdate()))
INSERT INTO @tblDates VALUES (getdate(), DATEADD(mi,19995,getdate()))

SELECT
      logged
      , resolved
      , CASE
            WHEN DATEDIFF(dd, logged, resolved) > 6 OR DATEPART(dw, resolved) < DATEPART(dw, logged) THEN
                        CASE
                              WHEN DATEPART(dw, logged) < DATEPART(dw, resolved) THEN ((DATEDIFF(hh, logged, resolved) - (DATEDIFF(dd, logged, resolved) * 16)) - 16)
                              ELSE (DATEDIFF(hh, logged, resolved) - (DATEDIFF(dd, logged, resolved) * 16)) - (DATEDIFF(wk, logged, resolved) * 16)
                        END
            WHEN DATEDIFF(dd, logged, resolved) > 0 THEN DATEDIFF(hh, logged, resolved) - (DATEDIFF(dd, logged, resolved) * 16)
            ELSE DATEDIFF(hh, logged, resolved)
      END WorkingHoursToResolve
      , DATEPART(dw, resolved) - DATEPART(dw, logged) --<0 =48
      , DATEDIFF(wk, logged, resolved) DiffInWeeks --0
FROM
      @tblDates
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.