Solve this puzzle: User Activity Log

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)

Solve this puzzle: User Activity Log

Author

Topic

otherones
Starting Member

3 Posts

Posted - 2009-06-25 : 19:12:11

I have a log table that stores activities that users perform on records in our system.

I am trying to write a sql statement that will show me how much time a user spends on each record.

** I am trying to keep the answer to be provided in one sql statement. That is to say RANK, ROW_NUMBER, and nested joins are allowed but INSERT INTO and temp tables are not.

** If a user works on an account (ie User: A, Record: 1 in the sample table below) and then switches accounts (ie Record: 2) but ultimately comes back to first accounts (Record: 1) --- each "session" they spent on the first record (Record: 1) should be timed separately

** Not necessarily relevant; however, I want to use this statement as a view that will be the datasource to build a cube from

** The source data set is of significant size ( > 5M rows)

Source Data (SELECT UserID, TimeStamp, RecordID FROM dbo.[Log] ORDER BY UserId, TimeStamp)


UserID	TimeStamp RecordID
A	12:01	1
A	12:02	1
A	12:03	1
A	12:04	2
A	12:15	2
A	12:20	1
A	12:21	1
B	12:02	3
B	12:03	3
B	12:04	1
B	12:05	1
C	12:30	4
C	12:31	5
C	12:35	5
C	12:36	2

Desired Result


UserID	StartTi EndTim  Record  Duration
A	12:01	12:04	1	3
A	12:04	12:20	2	16
A	12:20	12:21	1	1
B	12:02	12:04	3	2
B	12:04	12:05	1	1
C	12:30	12:31	4	1
C	12:31	12:36	5	5
C	12:36	12:36	2	0

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 21:15:44

[code]SELECT UserID,
MIN(CONVERT(VARCHAR,TimeStamp,108)) as StartDate,
MAX(CONVERT(VARCHAR,TimeStamp,108)) as Enddate,
RecordID,
DateDiff(n,MIN(TimeStamp),MAX(TimeStamp))AS Duration
FROM Log
GROUP BY RecordID, UserID
ORDER BY UserId, TimeStamp[/code]
Something liddat?Sorry i still figuring how to extract only minute from datetime

otherones
Starting Member

3 Posts

Posted - 2009-06-25 : 21:35:59

No - unfortunatley, that won't do it due to this REQ:

** If a user works on an account (ie User: A, Record: 1 in the sample table below) and then switches accounts (ie Record: 2) but ultimately comes back to first accounts (Record: 1) --- each "session" they spent on the first record (Record: 1) should be timed separately

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 21:58:10

ooo, i think that needs a recursive query...still haven't got time to understand that...so sorry

Type42Destroyer
Starting Member

1 Post

Posted - 2009-06-25 : 22:39:34

SELECT userid,
Min(TIMESTAMP) AS starttime,
Max(TIMESTAMP) AS endtime,
Datediff(s,Min(TIMESTAMP),Max(TIMESTAMP)) AS duration,
recordid,
session
FROM (SELECT row - Rank()
OVER(PARTITION BY recordid ORDER BY row) session,
*
FROM (SELECT Row_number()
OVER(ORDER BY userid, TIMESTAMP) row,
userid,
TIMESTAMP,
recordid) a) b
GROUP BY userid,
recordid,
session

:), R

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 23:09:49

quote:
Originally posted by Type42Destroyer

SELECT userid,
Min(TIMESTAMP) AS starttime,
Max(TIMESTAMP) AS endtime,
Datediff(s,Min(TIMESTAMP),Max(TIMESTAMP)) AS duration,
recordid,
session
FROM (SELECT row - Rank()
OVER(PARTITION BY recordid ORDER BY row) session,
*
FROM (SELECT Row_number()
OVER(ORDER BY userid, TIMESTAMP) row,
userid,
TIMESTAMP,
recordid) a) b
GROUP BY userid,
recordid,
session

:), R

That's a neat trick

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 23:15:18

Sorry if im lack of knowledge...how to select without from??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 23:18:49

quote:
Originally posted by waterduck

Sorry if im lack of knowledge...how to select without from??

actually there is a typo. Missed out the FROM dbo.[Log]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 23:21:43

re-post of Type42Destroyer's solution.

added the missing FROM statement


SELECT 
	UserID,
	MIN(timestamp) AS starttime,
	MAX(timestamp) AS endtime,
	DATEDIFF(s,MIN(timestamp),MAX(timestamp)) AS duration,
	RecordID,
	session
FROM 
(
	SELECT 	row - Rank() OVER(PARTITION BY RecordID ORDER BY row) session,
		*
	FROM 	
	(
		SELECT 	Row_number() OVER(ORDER BY UserID, timestamp) row,
			UserID,
			timestamp,
			RecordID
		FROM	dbo.[Log]
	) a
) b
GROUP BY 
	UserID,
	RecordID,
	session

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

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-25 : 23:40:03

woops sorry about that...i was trying with recursive but fail haha many thanks to khtan and vy vy thank to otherones

ps otherones you are really smart!!!

otherones
Starting Member

3 Posts

Posted - 2009-06-26 : 11:44:46

Wow --- Type42Destroyer has a damn good solution.

Congrats

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources