Find Average time between 2 dates

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 between 2 dates

Author

Topic

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-25 : 09:01:20

what I have is a check in/out table. What I want to do i track the average time a people are taking at the event. My table is as follows, stations bits are whtheret hey wnet to a certain station or not.

intAttendId intEventId strssn dtTimeIN dtTimeOut (stationBits)

With this code I got the total time of the event, but now I need the average time overall that people spent at the event.

select Cast(DATEDIFF(hh, min(dtTimeIn), Max(dtTimeout)) as varchar) + ':' + cast(DATEDIFF(mi,DATEADD(hh,DATEDIFF(hh, min(dtTimeIn), Max(dtTimeout)), min(dtTimeIn)),Max(dtTimeout)) as varchar) From tblSRPAttendance where inteventId = @SRPEvent

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:17:38

seems like this??


SELECT intEVentId,CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,min(dtTimeIn),max(dtTimeOut))*1.0),0),108)
FROM Table
GROUP BY intEvevtId

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-25 : 11:35:30

HI,
I get an error that it
Cannot perform and aggregate function on an expression containing an aggregate or a subquery

SELECT intEVentId,CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,min(dtTimeIn),max(dtTimeOut))*1.0),0),108)
from tblSRPAttendance where intEventId = 145
GROUP BY intEventid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 11:50:53

[code]
SELECT intEVentId,CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,TimeIn,timeOut)*1.0),0),108)
FROM
(
SELECT intEVentId,min(dtTimeIn) AS TimeIn,max(dtTimeOut) as timeOut
from tblSRPAttendance where intEventId = 145
GROUP BY intEventid
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-25 : 13:50:33

Hi,

Originall it was givein same stime but I saw that the Min time and mazx time wehre still in there, so i took them out but now it gives an average time of 22.12.27 The total time of the event was 8 hours and 10 minutes. How can an average check in /out time be 22 hours?

SELECT intEventId,
CONVERT(varchar(8),DATEADD(ss,AVG(DATEDIFF(ss,TimeIn,timeOut)*1.0),0),108)
FROM
(
SELECT intEVentId, dtTimeIn AS TimeIn, dtTimeOut as timeOut
from tblSRPAttendance where intEventId = 145
GROUP BY intEventid, dtTimeIn, dtTimeOut
)t
GROUP BY intEventid

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 13:53:53

can you show some sample data and explain?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

kdeutsch
Yak Posting Veteran

83 Posts

Posted - 2011-07-25 : 13:55:53

ok< never mind it works, i had a few different days in there so it was messign up the average time. thanks for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-25 : 14:04:16

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources