how to Average time on issues - SQL Server Forums

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)

how to Average time on issues

Author

Topic

fts
Starting Member

4 Posts

Posted - 2009-04-03 : 15:36:16

This query is to find the average time (from submitterTable) spent on each issue type (problemName from IssuesTable). There can be multiple commentes for each Issue, they go into the commentsTable, and who submitted it and how long it took is added to the SubmitterTable.so my code so far is,.

I also hae a issuesTable as follows.
IssueID int
ProblemName varchar(50)
Resolved varchar(10)
Resoultion varchar(500)
Escalated varchar(50)
SubmittedDate datetime
DateCreated datetime
DateClosed datetime

And i have a SubmitterTable as follows.
SubmitterID int
SubmitterEID varchar(10)
IssueID int
SubmittedDate datetime
StartTime datetime
EndTime datetime
StartDate datetime
EndDate datetime

And my sql statement that im having a hard time so far with is

SELECT AVG(DATEDIFF(n, SubmitterTable.StartTime, SubmitterTable.EndTime)) * COUNT(issuestable.problemname) AS AvgerageMinutes, IssuesTable.ProblemName
FROM SubmitterTable INNER JOIN
IssuesTable ON SubmitterTable.IssueID = IssuesTable.IssueID
WHERE (issuestable.SubmittedDate BETWEEN @Param3 AND @Param4)
GROUP BY IssuesTable.ProblemName

If anyone has any ideas or help in any way, thanks so much!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-04-03 : 16:10:38

If you want the average minutes by ProblemName then I'm not sure why you're Multiplying the average by the count.

shouldn't it just be:


select IssuesTable.ProblemName
       ,AVG(DATEDIFF(n, SubmitterTable.StartTime, SubmitterTable.EndTime)) as AvgerageMinutes
from...
where...
group by IssuesTable.ProblemName

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources