Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Average Call Times per day

Status
Not open for further replies.

sparkbyte

Technical User
Sep 20, 2002
879
US
I need a query that will give me avg call durations on a per/day.

I have a query that will give the call times but not sure how to get it to get the rest of the way.

SQL:
select 
	--s.seconds / 3600  hrs,
	--s.seconds / 60 - (seconds / 3600 ) * 60 mins,
	--s.seconds - (s.seconds / 60) * 60   seconds,
	cast(s.seconds / 3600 as varchar) + ':' +
	cast((s.seconds / 60 - (seconds / 3600 ) * 60) as varchar) + ':' +
	cast((s.seconds - (s.seconds / 60) * 60) as varchar) ElapsedTime
from (select datediff(second, [Start Time & Date], SEIPrint) as seconds From dbo.Activity
		Where ([Start Time & Date] BETWEEN '2013-06-1' AND '2013-06-7')
		AND SEIPrint Is Not Null) s

Thanks

John Fuhrman
 
How does this work for you?

Code:
SELECT x.StartDate
	,cast(s.AvgSecondsPerCall / 3600 as varchar) + ':' +
	cast((s.AvgSecondsPerCall / 60 - (AvgSecondsPerCall / 3600 ) * 60) as varchar) + ':' +
	cast((s.AvgSecondsPerCall - (s.AvgSecondsPerCall / 60) * 60) as varchar) ElapsedTime
FROM (SELECT s.StartDate
		,SUM(s.Seconds) / COUNT(*) AS AvgSecondsPerCall
	FROM (select [b]Cast([Start Time & Date] AS date) AS StartDate,[/b]
			datediff(second, [Start Time & Date], SEIPrint) as seconds From dbo.Activity
			Where ([Start Time & Date] BETWEEN '2013-06-1' AND '2013-06-7')
			AND SEIPrint Is Not Null) s
	GROUP BY StartDate) x
ORDER BY StartDate

--JD
"Behold! As a wild ass in the desert go forth I to my work."

 
Try this:

Code:
Select DateAdd(Day, DateDiff(Day, 0, [Start Time & Date]), 0) As CallDate,
       Convert(VarChar(10), DateAdd(Second, Avg(datediff(second, [Start Time & Date], SEIPrint)), 0), 108) as Duration
From   dbo.Activity
Where  [Start Time & Date] >= '2013-06-1'
       AND [Start Time & Date] < '2013-06-7'
       AND SEIPrint Is Not Null
Group By DateAdd(Day, DateDiff(Day, 0, [Start Time & Date]), 0)
Order By DateAdd(Day, DateDiff(Day, 0, [Start Time & Date]), 0)

Note that calls that start on one day and end on another will be counted on the day the call started, not the day the call ended.



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
George that seems to do it.
BTW. Nice trick with the convert!

Thanks

John Fuhrman
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top