Time spent Logged in - 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 2005 Forums

Transact-SQL (2005)

Time spent Logged in

Author

Topic

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-16 : 11:45:09

I have a SQL table that stores the date/time everytime a user logs in and the date/time they logged out of our application. What I would like to do is produce stats based on the time they have spent within our application.

Does anyone know how this can be done? I have the stats for how many times they have logged in but would like to know the time spent in the system as well

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 11:48:56

Use DATEDIFF(ss,logintime,logouttime) gives interval spend by logged in user in seconds.

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-16 : 12:14:14

cool got that working now but it won't sum up 0 hours?

SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
GROUP BY ID, TimeAllocated, TimeLastAccessed

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-16 : 12:40:54

why don't you use Datediff as Visakh suggested?
And remove distinct as you are using Group by.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 13:12:01

quote:
Originally posted by starnold

cool got that working now but it won't sum up 0 hours?

SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
GROUP BY ID, TimeAllocated, TimeLastAccessed

how do you expect it to sum to 0?

X002548
Not Just a Number

15586 Posts

Posted - 2008-09-16 : 13:21:44

my guess is they are talking about parts of an hour

how do you know which log in equals what log out?

Also what purpose does this have

I've logged on, went to lunch, came back and logged out

what value is in that?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-16 : 14:42:04

I have removed distinct and it gives me more than 1 line per id! What I want is the total amount of time spent in the application per ID. What I mean by 0 hours is that it will return

ID NoHours
1 0
1 10
2 15
2 0

What I would like is just a unique ID with the total no of hours (whether they have just logged in and log straight back out)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-09-16 : 14:49:32

Why don't you only Group by ID?

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-16 : 14:55:41

I have tried that and that doesn't work =(

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-09-16 : 22:14:57

quote:
Originally posted by starnold

I have tried that and that doesn't work =(

Show us the data and your query and the result that you want so that we know how it is not working

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 03:12:20

quote:
Originally posted by starnold

I have removed distinct and it gives me more than 1 line per id! What I want is the total amount of time spent in the application per ID. What I mean by 0 hours is that it will return

ID NoHours
1 0
1 10
2 15
2 0

What I would like is just a unique ID with the total no of hours (whether they have just logged in and log straight back out)

How will you get 0 values when you've only logged in information on your table.Where is main information for ID held?

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-17 : 04:28:41

Below is the data and how it is stored

ID UserID TimeAllocated TimeLastAccessed System
Acerta1 1 2008-03-20 09:43:53.000 2008-03-20 09:44:27.000 Acerta
Acerta1 1 2007-09-27 14:14:11.000 2007-09-27 15:24:32.000 Acerta
Acerta1 1 2008-04-01 16:24:43.000 2008-04-01 16:38:18.000 Acerta
Acerta1 1 2008-03-20 09:43:53.000 2008-03-20 09:44:27.000 Acerta
Acerta1 1 2007-09-27 14:14:11.000 2007-09-27 15:24:32.000 Acerta
Acerta1 1 2008-04-01 16:24:43.000 2008-04-01 16:38:18.000 Acerta
Acerta2 2 2007-09-28 10:12:22.000 2007-09-28 10:14:30.000 Acerta
Acerta2 2 2007-09-26 15:28:19.000 2007-09-26 15:28:25.000 Acerta
Acerta2 2 2007-09-14 10:37:19.000 2007-09-14 10:40:10.000 Acerta

I then run:
Posted - 09/16/2008 : 12:14:14
--------------------------------------------------------------------------------

cool got that working now but it won't sum up 0 hours?

SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
GROUP BY ID, TimeAllocated, TimeLastAccessed

Which gives me:
ID NoHours
Acerta1 0
Acerta1 2
Acerta2 0
Acerta2 8
Acerta2 4
Acerta2 2
Acerta2 6

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 04:53:59

quote:
Originally posted by starnold

Below is the data and how it is stored

ID UserID TimeAllocated TimeLastAccessed System
Acerta1 1 2008-03-20 09:43:53.000 2008-03-20 09:44:27.000 Acerta
Acerta1 1 2007-09-27 14:14:11.000 2007-09-27 15:24:32.000 Acerta
Acerta1 1 2008-04-01 16:24:43.000 2008-04-01 16:38:18.000 Acerta
Acerta1 1 2008-03-20 09:43:53.000 2008-03-20 09:44:27.000 Acerta
Acerta1 1 2007-09-27 14:14:11.000 2007-09-27 15:24:32.000 Acerta
Acerta1 1 2008-04-01 16:24:43.000 2008-04-01 16:38:18.000 Acerta
Acerta2 2 2007-09-28 10:12:22.000 2007-09-28 10:14:30.000 Acerta
Acerta2 2 2007-09-26 15:28:19.000 2007-09-26 15:28:25.000 Acerta
Acerta2 2 2007-09-14 10:37:19.000 2007-09-14 10:40:10.000 Acerta

I then run:
Posted - 09/16/2008 : 12:14:14
--------------------------------------------------------------------------------

cool got that working now but it won't sum up 0 hours?

SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
GROUP BY ID, TimeAllocated, TimeLastAccessed

Which gives me:
ID NoHours
Acerta1 0
Acerta1 2
Acerta2 0
Acerta2 8
Acerta2 4
Acerta2 2
Acerta2 6

How do you think this sum upto 0?

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-17 : 05:01:09

I dont understand?

What I want is just 1 row per ID with the total NoHours. So instead of:

ID NoHours
Acerta1 0
Acerta1 2
Acerta2 0
Acerta2 8
Acerta2 4
Acerta2 2
Acerta2 6

I would like a query to give me:
ID NoHours
Acerta1 2
Acerta2 20

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 05:18:13

quote:
Originally posted by starnold

I dont understand?

What I want is just 1 row per ID with the total NoHours. So instead of:

ID NoHours
Acerta1 0
Acerta1 2
Acerta2 0
Acerta2 8
Acerta2 4
Acerta2 2
Acerta2 6

I would like a query to give me:
ID NoHours
Acerta1 2
Acerta2 20

sorry i misinterpreted your question. do as below:-

SELECT ID,SUM(NOHOURS) AS Total
FROM
(
SELECT distinct ID, sum(isnull(DATEDIFF([HOUR], TimeAllocated, TimeLastAccessed),0)) AS NOHOURS
FROM Sessions
GROUP BY ID, TimeAllocated, TimeLastAccessed 
)t
GROUP BY ID

starnold
Yak Posting Veteran

83 Posts

Posted - 2008-09-17 : 05:19:51

LEGEND!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources