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 - 11/06/2012 : 03:27:49
