Time difference only during business hours

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Time difference only during business hours

Author

Topic

mericlese
Starting Member

11 Posts

Posted - 2007-07-24 : 12:51:26

Heya all. I have this query that more or less looks at the support calls opened for a specific date range, finds the amount of time it takes for them to be assigned to a technician. Thing is we only want to record minutes during business hours. Does anyone have any ideas?

SELECT SUM(TimeSpan) AS TotalMinutes FROM (
SELECT *, DateDiff(n, OpenDate, Forwarded ) AS Timespan FROM (
SELECT Calls.CallID, Calls.OpenDate, Calls.AnalystID, (
SELECT TOP 1 CallActions.ActionDateTime FROM CallActions
WHERE CallActions.CallID = Calls.CallID
AND CallActions.ActionTaken = 'Assign' and CallActions.CurrentAnalyst = 0
ORDER BY CallActions.CallID, CallActionID) AS Forwarded
FROM Calls ) x
inner join Phone.DBO.Extensions as ext on ext.ID = AnalystID
WHERE x.Forwarded IS NOT NULL AND
OpenDate between '7/13/2007' and '7/16/2007 11:59:59 PM'
and ext.class = 'Bank') y

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 13:06:27

Try this

SELECT		SUM(DATEDIFF(SECOND, d.OpenDate, d.ActionDateTime) / 60.0) AS TotalMinutes
FROM		(		
			SELECT		c.CallID,
					c.OpenDate,
					c.AnalystID,
					ca.ActionDateTime,
					ROW_NUMBER() OVER (PARTITION BY c.CallID ORDER BY ca.CallID, ca.CallActionID) AS RecID
			FROM		Calls AS c
			INNER JOIN	CallActions AS ca ON ca.CallID = c.CallID
			WHERE		ca.ActionTaken = 'Assign'
					AND ca.CurrentAnalyst = 0
					AND c.OpenDate >= '7/13/2007'
					AND c.OpenDate < '7/17/2007' 
					AND DATEDIFF(HOUR, 0, c.OpenDate) % 24 BETWEEN 8 AND 16 -- 8:00 am to 4:59 pm
		) AS d
INNER JOIN	Phone.DBO.Extensions AS ext ON ext.ID = d.AnalystID 
WHERE		d.RecID = 1
		AND ext.Class = 'Bank'

E 12°55'05.76"
N 56°04'39.42"

mericlese
Starting Member

11 Posts

Posted - 2007-07-24 : 14:17:35

Would that account for weekends as well?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 14:31:06

It depends if you want them in or out?

E 12°55'05.76"
N 56°04'39.42"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-24 : 14:32:07

[code]SELECT SUM(DATEDIFF(SECOND, d.OpenDate, d.ActionDateTime) / 60.0) AS TotalMinutes
FROM (
SELECT c.CallID,
c.OpenDate,
c.AnalystID,
ca.ActionDateTime,
ROW_NUMBER() OVER (PARTITION BY c.CallID ORDER BY ca.CallID, ca.CallActionID) AS RecID
FROM Calls AS c
INNER JOIN CallActions AS ca ON ca.CallID = c.CallID
WHERE ca.ActionTaken = 'Assign'
AND ca.CurrentAnalyst = 0
AND c.OpenDate >= '7/13/2007'
AND DATENAME(WEEKDAY, c.OpenDate) NOT IN ('Saturday', 'Sunday')
AND c.OpenDate < '7/17/2007'
AND DATEDIFF(HOUR, 0, c.OpenDate) % 24 BETWEEN 8 AND 16 -- 8:00 am to 4:59 pm
) AS d
INNER JOIN Phone.DBO.Extensions AS ext ON ext.ID = d.AnalystID
WHERE d.RecID = 1
AND ext.Class = 'Bank'[/code]

E 12°55'05.76"
N 56°04'39.42"

mericlese
Starting Member

11 Posts

Posted - 2007-07-24 : 16:54:45

Hmmm...I'm afraid that won't work. We will only have calls that are opened on weekdays. For example: someone calls in at 4:55 on Monday afternoon, the call is assigned at 8:05 Tuesday Morning it should return 10 minutes. Likewise if the call was opened on Friday and assigned on Monday you'd also get 10 minutes. If on the other hand you Opened a call on Monday, and it was assigned on Wednesday you'd get 490 minutes. Here's something I came up with to return the correct time given two date times:

declare @FirstDate as smalldatetime
declare @SecondDate as smalldatetime
declare @DateName as Varchar(15)
declare @timediff as integer
declare @days as integer
declare @counter as integer
declare @tempdate as smalldatetime
set @counter = 1
set @FirstDate = @OpenDate
set @SecondDate = @Forward
set @days = DateDiff(d, @FirstDate, @Forward)
if @days > 0
begin
set @timediff = DateDiff(n, @FirstDate, CONVERT(char(8),@FirstDate,10) +' 5:00:00 PM') + DateDiff(n, CONVERT(char(8),@Forward,10) + ' 8:00:00 AM', @Forward)
while @counter < @days
begin
set @tempdate = DateAdd(d,1,@FirstDate)
set @DateName = DateName(dw, @tempdate)
if @DateName != 'Saturday' and @DateName != 'Sunday'
set @timediff = @timediff + 480
set @counter = @counter + 1
end
end
else
set @timediff = DateDiff(n, @FirstDate, @SecondDate)
select @timediff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources