SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 create session id of visitor
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

amrit_SQl
Starting Member

India
3 Posts

Posted - 11/06/2012 :  03:27:49  Show Profile  Reply with Quote
I have a tale in which have visitor id and time stamp as fields.
I want to give user a session on the basis of his time stamp.

Session --> "Continuous inactivity of 30 mins or continuous activity of 12 hours"

Visitor Id SYS BuyOnline - c Event Time Session_ID
1000000008000000001 9/17/2012 10:26 1
1000000008000000001 9/17/2012 10:25 1
1000000008000000001 9/29/2012 20:58 2
1000000008000000001 9/28/2012 4:08 3
5319750341188788087 9/13/2012 18:36
5322111251524293660 9/9/2012 15:09
5322111251524293660 9/9/2012 15:07
5338424846665981062 9/20/2012 20:23
5357266771553889040 9/2/2012 10:47
5368045119857819550 9/29/2012 9:00
5388558803927621962 9/15/2012 15:20
5408608018041539776 9/28/2012 10:20
5408608018041539776 9/28/2012 10:20

how to divide visitors id on the basis of time as session id 1 2 3

-@mrit

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  07:59:10  Show Profile  Reply with Quote
Are you trying to assign session_id's to a bunch of existing rows, or are you trying to determine what the session id should be when a new row is entered (or both?)
Go to Top of Page

amrit_SQl
Starting Member

India
3 Posts

Posted - 11/06/2012 :  08:15:48  Show Profile  Reply with Quote
@ sunitabeck:

I am trying to create a new column "Session_id"
which should be on the rule "continous inactivity of 30 mins or continous activity of 12 hours"
that means the difference between two consecutive timestamp shud be greater than 30 mins to assign that id a new session else all entries will have same session.
eg.
Visitor_ID time diff Session_id
1 10:05 0 Session_1
1 10:20 00:15 Session_1
1 10:59 00:39 Session_2 (coz diff is > 30 mins)
2 11:35 00:36 Session_1 (diff>30 but id is diff)
2 11:45 00:10 Session_1

got it..... or i need to explain more


-@mrit
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  09:16:50  Show Profile  Reply with Quote
Give this a try. I have not actually tested the code because I don't have any test data. If it does not do what you are looking for, can you post some test data that can be copied and pasted to create a test table and data? This is only a select - if it works as expected, it can be easily changed into an update
;WITH cte1 AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY [Visitor Id] ORDER BY [Event Time]) AS RN
	FROM YourTable
),
cte2 AS
(
	SELECT
		*,
		1 AS session_id,
		[Event Time] AS StartEventTime
	FROM
		cte1
	WHERE
		RN = 1
	UNION ALL
	SELECT
		c1.*,
		CASE
			WHEN DATEDIFF(mm,c2.[Event Time],c1.[Event Time]) > 30
				OR DATEDIFF(mm,c2.StartEventTime,c1.[Event Time]) > 12*60
				THEN c1.session_id + 1
			ELSE c1.session_id 
		END,
		CASE
			WHEN DATEDIFF(mm,c2.[Event Time],c1.[Event Time]) > 30
				OR DATEDIFF(mm,c2.StartEventTime,c1.[Event Time]) > 12*60
				THEN c2.[Event Time] 
			ELSE c1.StartEventTime 
		END
	FROM
		cte1 c1
		INNER JOIN cte2 c2 ON c1.RN = c2.RN+1
)
SELECT * FROM cte2;
Go to Top of Page

amrit_SQl
Starting Member

India
3 Posts

Posted - 11/06/2012 :  10:06:46  Show Profile  Reply with Quote
@sunitabeck: TEST DATA


Visitor Event Time Session_id
1000000008000000001 9/17/12 10:26 AM
1000000008000000001 9/17/12 10:25 AM
1000000008000000001 9/29/12 8:58 PM
1000000008000000001 9/28/12 4:08 AM
5319750341188788087 9/13/12 6:36 PM
5322111251524293660 9/9/12 3:09 PM
5322111251524293660 9/9/12 3:07 PM
5338424846665981062 9/20/12 8:23 PM
5357266771553889040 9/2/12 10:47 AM
5368045119857819550 9/29/12 9:00 AM
5388558803927621962 9/15/12 3:20 PM
5408608018041539776 9/28/12 10:20 AM
5408608018041539776 9/28/12 10:20 AM
5408608018041539776 9/28/12 10:21 AM
5408608018041539776 9/28/12 10:21 AM
5397716585438180151 9/13/12 8:12 PM
5381025855429715532 9/12/12 12:17 PM
5378218116760046418 9/16/12 5:22 PM


this is test data.... rows are too many around 2 million so i m trying to avoid joins... can there be any other work around.


-@mrit
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/06/2012 :  10:40:03  Show Profile  Reply with Quote
For 2 million rows, what I posted would not be a good solution. You might try the quirky update here: http://www.sqlservercentral.com/articles/T-SQL/68467/

If you are on SQL 2012, you would have more options.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000