I need to combine these...somehow? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

I need to combine these...somehow?

Author

Topic

mericlese
Starting Member

11 Posts

Posted - 2007-07-19 : 12:39:32

Alright...I need these two pieces of SQL to play together nicely. First off we have:

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

This finds the time between when a Call is opened and when it is assigned, and then sums them, so that I can figure out an average later. Now, we want to only include the time spent during business hours. I saw similar problem on here, and worked up my own solution.

declare @stupiddate as smalldatetime
declare @somethingelse 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 @stupiddate = OpenDate
set @somethingelse = Forwarded
set @days = DateDiff(d, @stupiddate, @somethingelse)
if @days > 0
begin
set @timediff = DateDiff(n, @stupiddate, CONVERT(char(8),@stupiddate,10) +' 5:00:00 PM') + DateDiff(n, CONVERT(char(8),@somethingelse,10) + ' 8:00:00 AM', @somethingelse)
while @counter < @days
begin
set @tempdate = DateAdd(d,1,@stupiddate)
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, @stupiddate, @somethingelse)
select @timediff

This little piece of work only includes the minutes during business hours and not on weekends. So, how do I combine part 1 with part 2? I've been trying for half a day and I still haven't gotten it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources