By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
SELECT * INTO Session FROM (
VALUES
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Home ', '(entrance)', 24 , 5 ),
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Sales ', 'Home ', 24 , 10 ),
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Contact ', 'Sales ', 24 , 9 ),
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Sales ', '(entrance)', 5 , 5 ),
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Home ', '(entrance)', 35 , 30 ),
(CONVERT(DATETIME, '2018-01-01T20:20:00'), 'Sales ', 'Home ', 35 , 5 )) x(DATE_HOUR_MINUTE, Page , PrevPage, TotalDuration, Duration )
6 rows affected
;with sessionTree AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as sessionId
, 1 AS PageSeq
, *
FROM Session WHERE PrevPage = '(entrance)'
UNION ALL
SELECT prev.sessionId
, prev.PageSeq + 1
, next.*
FROM sessionTree prev
JOIN Session next
ON next.TotalDuration = prev.TotalDuration
AND next.PrevPage = prev.Page
AND next.date_hour_minute >= prev.date_hour_minute
)
SELECT * FROM sessionTree
order by sessionId, PageSeq
| sessionId | PageSeq | DATE_HOUR_MINUTE | Page | PrevPage | TotalDuration | Duration |
|---|---|---|---|---|---|---|
| 1 | 1 | 01/01/2018 20:20:00 | Home | (entrance) | 24 | 5 |
| 1 | 2 | 01/01/2018 20:20:00 | Sales | Home | 24 | 10 |
| 1 | 3 | 01/01/2018 20:20:00 | Contact | Sales | 24 | 9 |
| 2 | 1 | 01/01/2018 20:20:00 | Sales | (entrance) | 5 | 5 |
| 3 | 1 | 01/01/2018 20:20:00 | Home | (entrance) | 35 | 30 |
| 3 | 2 | 01/01/2018 20:20:00 | Sales | Home | 35 | 5 |