clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2591691 fiddles created (45709 in the last week).

CREATE TABLE TEST ( DTTM DATETIME, SESSION_ID INT ) INSERT INTO TEST (DTTM, SESSION_ID) select '2016-08-29 14:24:28.450', 297692378 union select '2017-04-13 23:54:53.760', 297692378 union select '2017-04-13 23:59:53.477', 297692378 union select '2017-04-14 00:04:52.897', 297692378 union select '2017-04-14 00:04:53.790', 297692378 union select '2017-04-14 00:44:53.790', 297692378
6 rows affected
 hidden batch(es)


ALTER TABLE TEST ADD [RANK] int;
 hidden batch(es)


SELECT DTTM, SESSION_ID, DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN FROM TEST
DTTM SESSION_ID DIF_MIN
29/08/2016 14:24:28 297692378 0
13/04/2017 23:54:53 297692378 327450
13/04/2017 23:59:53 297692378 5
14/04/2017 00:04:52 297692378 5
14/04/2017 00:04:53 297692378 0
14/04/2017 00:44:53 297692378 40
 hidden batch(es)


DECLARE @dttm datetime, @session_id int, @diff_min int, @acm_diff int, @rank int, @last_dttm datetime; SET @diff_min = 0; SET @acm_diff = 0; SET @rank = 0; SET @last_dttm = NULL; DECLARE curMin CURSOR FAST_FORWARD FOR SELECT DTTM, SESSION_ID, DATEDIFF(minute, COALESCE(LAG(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM), DTTM) DIF_MIN FROM TEST OPEN curMin; FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min; WHILE @@FETCH_STATUS = 0 BEGIN IF @last_dttm IS NULL OR @acm_diff + @diff_min > 20 BEGIN SET @rank = @rank + 1; SET @acm_diff = 0; END ELSE BEGIN SET @acm_diff = @acm_diff + @diff_min; END UPDATE TEST SET [RANK] = @rank WHERE DTTM = @dttm AND SESSION_ID = @session_id; SET @last_dttm = @dttm; FETCH NEXT FROM curMin INTO @dttm, @session_id, @diff_min; END CLOSE curMin; SELECT DTTM, SESSION_ID, DATEDIFF(minute, DTTM, COALESCE(LEAD(DTTM) OVER (ORDER BY DTTM, SESSION_ID), DTTM)) DIF_MIN, [RANK] FROM TEST ORDER BY DTTM, SESSION_ID;
DTTM SESSION_ID DIF_MIN RANK
29/08/2016 14:24:28 297692378 327450 1
13/04/2017 23:54:53 297692378 5 2
13/04/2017 23:59:53 297692378 5 2
14/04/2017 00:04:52 297692378 0 2
14/04/2017 00:04:53 297692378 40 2
14/04/2017 00:44:53 297692378 0 3
 hidden batch(es)