SHARE
TWEET

Untitled

a guest Feb 27th, 2020 76 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DECLARE @sessionsCount int = 1;
  2. DECLARE @timeLimitBetweenSessions int = 300;
  3. IF OBJECT_ID('tempdb..#TestTable') IS NOT NULL DROP TABLE #TestTable
  4. SELECT
  5.         a.Id, a.User_Id UserId1,
  6.         b.User_Id UserId2,
  7.         a.CreateDateTime StartActivity,
  8.         b.CreateDateTime EndActivity,
  9.         a.RowNum1,
  10.         b.RowNum2,
  11.         @sessionsCount as 'SessionNumber'
  12.   INTO #TestTable
  13.   FROM (
  14.   select Id,
  15.         User_Id ,
  16.         CreateDateTime
  17.         ,ROW_NUMBER() OVER (ORDER BY [User_Id], [CreateDateTime]) AS RowNum1
  18.   from  [dbo].[PageLoadings]) as a
  19.   JOIN (
  20.   SELECT [Id]
  21.       ,[User_Id]
  22.       ,[CreateDateTime]
  23.       ,ROW_NUMBER() OVER (ORDER BY [User_Id], [CreateDateTime]) AS RowNum2
  24.         FROM [dbo].[PageLoadings]) as b
  25.         on a.RowNum1 = (b.RowNum2 - 1)
  26.     UPDATE #TestTable SET
  27.         SessionNumber =
  28.         CASE WHEN UserId1 <> UserId2 THEN 0
  29.         ELSE CASE WHEN DATEDIFF(SECOND, StartActivity, EndActivity) > @timeLimitBetweenSessions THEN 0
  30.             ELSE @sessionsCount
  31.             END
  32.         END,
  33.         @sessionsCount =
  34.         CASE WHEN UserId1 <> UserId2 THEN @sessionsCount + 1
  35.         ELSE
  36.             CASE WHEN DATEDIFF(SECOND, StartActivity, EndActivity) > @timeLimitBetweenSessions THEN @sessionsCount + 1
  37.             ELSE @sessionsCount
  38.             END
  39.         END;
  40.     --Костыль для последней строки на случай, когда между последней и предпоследней сессий прошло > 5 минут
  41.     WITH LastRecord AS
  42.     (
  43.     SELECT *
  44.     FROM #TestTable
  45.     WHERE RowNum2 = (SELECT MAX(RowNum2) FROM #TestTable)
  46.     )
  47.     UPDATE LastRecord SET SessionNumber =
  48.         CASE WHEN SessionNumber = 0 THEN @sessionsCount
  49.             ELSE 0
  50.             END,
  51.         StartActivity =
  52.         CASE WHEN SessionNumber = 0 THEN EndActivity
  53.             ELSE StartActivity
  54.             END;
  55.     UPDATE #TestTable SET EndActivity = DATEADD(minute, 5, EndActivity)
  56.         WHERE EndActivity IN (SELECT max(EndActivity)
  57.         FROM #TestTable
  58.         WHERE SessionNumber <> 0
  59.         GROUP BY SessionNumber)
  60.     SELECT UserId2 as UserId, min(StartActivity) as StartActivity, max(EndActivity) as EndActivity FROM #TestTable
  61.         WHERE SessionNumber <> 0
  62.         GROUP BY SessionNumber, UserId2
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top