Sum multiple datetime and Average

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)

Sum multiple datetime and Average

Author

Topic

fts
Starting Member

4 Posts

Posted - 2009-03-27 : 15:38:59

I am trying to sum the values from all the records in one table that have the same issueid. the code i have so for only seems to return the first record with that issue id. Mabye im going at this the wrong way. Or i know there has to be a way to do this, the code i have so far is.

SELECT IssuesTable.ProblemName, SUM(DATEDIFF(n, SubmitterTable.StartTime, SubmitterTable.EndTime)) AS Minutes
FROM SubmitterTable LEFT OUTER JOIN
IssuesTable ON SubmitterTable.IssueID = IssuesTable.IssueID
GROUP BY IssuesTable.ProblemName

So after i get this one to add all the comments together then the next query i am going to have to do is get the average time in minutes for each issue type. That would be a separate query though,

thanks for any help.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-27 : 16:39:20

quote:
return the first record with that issue id

I didn't understand what the issue is ..

fts
Starting Member

4 Posts

Posted - 2009-03-27 : 17:11:44

i have a issues table open and close tickets so to speak, where there can be multiple issues with the same problemName and diffrent helpdesk people can insert comments on the issue into a comments table. I have the submitterTable that keeps track of how long it took them to help a user and add the descripton. I now need to add all the time it took to add the description and help the user to come to a resoultion on the issue. So i need to know much time total for all the comments was spent on each type of problem.
the code above seems to only grab the first DATEDIFF form the submitterTable, instead of adding all the DATEDIFF from submitterTable with the same problemName, like i want. ... I know im just doing something stupid, but im a complete nube at sql... i think i need to add in the commentsTable to link the submitterID to the issue id... going to try that next...
Thanks for any help or ideas..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-28 : 04:09:39

your problem is not clear...try to give some sample data in below format and explain

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

fts
Starting Member

4 Posts

Posted - 2009-03-30 : 18:45:28

I have a CommentsTable as follows.
CommentsID int
IssueID int
IssueDescription varchar(500)
SubmitDate datetime
SubmitterEID varchar(10)

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

I want add all the time spent on the issue from the submitterTable start and end times. But i dont know what im doing wrongg when it comes to returing and summing all the comments for an issue. So i want distinct problem name and how much time total did we spend trying to fix that problem.
After this i am going to want another report to average the time spent on each distinct problemName. I think that SQL statement would look like this one, with only changing SUM to AVG. That is if i could get this first one to work..
Thanks for any help you may have.!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources