Query Help - 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

SQL Server Development (2000)

Query Help

Author

Topic

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-04 : 00:47:06

I have a table with data like-

Agent ID- StartTime - Event
1-2006-01-23 11:00:00.000 -Logged In
1-2006-01-23 11:15:00.000 -Walkaway
1-2006-01-23 11:20:00.000 -RW
1-2006-01-23 11:26:00.000 -Logout
3-2006-01-23 10:30:00.000 -Logged In
3-2006-01-23 10:40:00.000 -Logout
6-2006-01-23 14:12:00.000 -Logged In
6-2006-01-23 15:00:00.000 -Logout

I want the result as:

AgentID::StartTime :: Event :: Login Duration
1::2006-01-23 11:00:00.000 ::Logged In :: 00:00:00
1::2006-01-23 11:15:00.000 ::Walkaway:: 00:00:00
1::2006-01-23 11:20:00.000 ::RW:: 00:00:00
1::2006-01-23 11:26:00.000 ::Logout:: 00:26:00
3::2006-01-23 10:30:00.000 ::Logged In:: 00:00:00
3::2006-01-23 10:40:00.000 ::Logout:: 00:10:00
6::2006-01-23 14:00:00.000 ::Logged In:: 00:00:00
6::2006-01-23 15:00:00.000 ::Logout:: 01:00:00

Here Login Duration is in hh:mm:ss format. Don't bother about the same format I just needed the time diff. Also when the event is 'Logout' then the diff between its related 'Logged In' & 'Logout' will be the Login Duration.

Please help me 4 this. I did the query for obtaining the diff between its related 'Logged In' & 'Logout' that is 00:26:00, 00:10:00, 01:00:00 etc but I have to show 00:00:00 for rest records

Njoy Life

swatib
Posting Yak Master

173 Posts

Posted - 2006-02-04 : 01:47:16

Friends I solved the query using Left outer join...........

Njoy Life

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources