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;
create session id of visitor
Posted - 2012-11-06 : 03:27:49
