Time logged out - 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 2000 Forums

Transact-SQL (2000)

Time logged out

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-06-18 : 10:51:15

I have a table similar to the below (pipes signify column seperation):

AgentID | LogOnTime | LogOffTime
224 | 18/06/2007 11:53:06 | NULL
224 | 18/06/2007 10:59:05 | 18/06/2007 11:50:04
224 | 18/06/2007 09:58:05 | 18/06/2007 10:05:21
112 | 18/06/2007 09:50:04 | 18/06/2007 10:39:34
112 | 18/06/2007 08:55:07 | 18/06/2007 09:47:56

I need to construct a query that shows the Total Login and Logout times for an agent over a given time period. To get the Login total is easy:

SUM(datediff(second,a.LogOnTime, ISNULL(a.LogOffTime, GETDATE())))

I cannot figure out how I can get the Total time an agent has spent Logged Out though. Perhaps I am over-complicating it in my head but I just cannot see this. Can anyone offer me any help please? I can post DDL etc if this helps.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 10:57:05

total time an agent spent logged out is calculated starting from when ?

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-06-18 : 11:08:08

This will be from the time the user states. I have posted the full sample code I have been using below. For this I have just used Today (GETDATE()) as an example:

SELECT		a.AgentID,
			dbo.udf_TimeFormat(SUM(datediff(second,a.LogOnTime, ISNULL(a.LogOffTime, GETDATE())))) as [Total Login Time]
FROM		dbo.ActionSession a
WHERE		CONVERT(Varchar,a.LogOnTime, 112) 
			BETWEEN CONVERT(Varchar,GETDATE(),112) and CONVERT(Varchar,GETDATE(),112)
GROUP BY	a.AgentID

The UDF is there just to make the times more readable.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-18 : 11:31:55

isn't it : [Total Logout Time] = [Total Time] - [Total Login Time]

[Total Time] = [Start Time] - [End Time]
[End Time] = Current Time or [Last Logout Time]
[Start Time] = First Login Time ?

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2007-06-18 : 13:06:48

It's been a long day.