Average Call Times per day

[sparkbyte]

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.

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

 

[JohnDuncanTB]

How does this work for you?

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."

 

[gmmastros]

Try this:

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

 

[sparkbyte]

George that seems to do it.
BTW. Nice trick with the convert!

Thanks

John Fuhrman