Find Average time - 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 2008 Forums

Transact-SQL (2008)

Find Average time

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-25 : 15:30:03

What I have is a table of answers and I want to find the average time it takes for a set of questions. My table is as follows.
IntDataId, PersonnelId, questionId, intAnswer, dtLogged
1 5623652 2 1 2011-07-22 13:32:51.000

So what I am trying to do is find the average time it takes to go through a question set.
Here is the quesry but this is the average time between 2 dates in my example above I only have one date field. And this is giveing me

00:00:00

SELECT CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,TimeIn,timeOut)*1.0),0),108)
FROM
(
SELECT eh.DtLogged AS TimeIn, eh.dtLogged as timeOut
from tblSRPEventData as eh INNER JOIN
tblSRPQuestion as q on q.intQuestionId = eh.intQuestionId
where q.intCategoryID = 1 AND intSrpAttendId in (Select intSrpAttendId from tblSRPAttendance where intEventId = 145)
)t

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-25 : 15:50:40

I didn't quite follow the logic you are trying to implement, but from the semantics of it, the result would be zero. Is there a typo of some sort? Specifically, look at the two lines I have shown in red. They are the same, and you are taking the average of the difference between those two in the outer query, which of course, would be zero.

SELECT
   CONVERT(
       VARCHAR(8),
       DATEADD(ss, AVG(DATEDIFF(ss, TimeIn, TIMEOUT) * 1.0), 0),
       108
   )
FROM
   (
       SELECT
          eh.DtLogged AS TimeIn,
          eh.dtLogged AS TIMEOUT
       FROM
          tblSRPEventData AS eh
          INNER JOIN tblSRPQuestion AS q
             ON  q.intQuestionId = eh.intQuestionId
       WHERE
          q.intCategoryID = 1
          AND intSrpAttendId IN (SELECT
                                    intSrpAttendId
                                 FROM
                                    tblSRPAttendance
                                 WHERE
                                    intEventId = 145)
   )t

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-25 : 15:55:27

Don't both of you get the same value for TimeIn and TimeOut?

Maybe this?


select avg(DurationSeconds)
from   (
       select eh.intQuestionId, datediff(ss, min(TimeIn), max(TimeIn)) as DurationSeconds
       from tblSRPEventData as eh INNER JOIN
       tblSRPQuestion as q on q.intQuestionId = eh.intQuestionId
       where q.intCategoryID = 1 AND intSrpAttendId in (Select intSrpAttendId from tblSRPAttendance where intEventId = 145)
       group by eh.intQuestionId
       ) d

Be One with the Optimizer
TG

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-25 : 20:40:43

quote:
Originally posted by TG

Don't both of you get the same value for TimeIn and TimeOut?

Maybe this?

Yes, that is what I was trying to say by highlighting the two columns. There are two tables in the query, but kdeutsch has given the schema only for one table - so not clear to me how to use the second table.

Also, not clear to me whether the average of each questionId for a group of users is required, or the average time for a question for each personnelId, or the average for the IntDataId for all personnelIds and all questionIds is the objective.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources