Assigning ranking based on difference in two rows in a SQL table

Okay - I used this sample data

declare @new_table table ([name] nvarchar(50), login_time time);
insert into @new_table([name], login_time) values
('user1', '10:00'),('user1', '11:59'),
('user1', '12:00'),('user1', '14:01'),
('user1', '15:00'),('user1', '15:05'),
('user1', '18:00'),('user1', '21:01');

Firstly, I need some data to apply the rule

Quote:

if another login was made within 2 hours following a prior login, it considered the same login session, otherwise, it is a new session.

I can calculate that using the LAG function[^]. I've also added in a ROW_NUMBER() just so I can refer to specific data

SELECT ROW_NUMBER() OVER (Partition by [name] order by [name],login_time) as rn
	,[name], [login_time]
	,datediff(MI, 
		LAG(login_time, 1, DATEADD(HH, -3, login_time)) over (Partition by [name] order by [name],login_time)
		, login_time) AS Timediff
FROM @new_table;

giving results (note the difference is in minutes)

rn	name	login_time			Timediff
1	user1	10:00:00.0000000	180
2	user1	11:59:00.0000000	119
3	user1	12:00:00.0000000	1
4	user1	14:01:00.0000000	121
5	user1	15:00:00.0000000	59
6	user1	15:05:00.0000000	5
7	user1	18:00:00.0000000	175
8	user1	21:01:00.0000000	181

In fact I'm not really interested in the actual number of minutes, only if it is > 120 (2 hours). So I'll actually use

,CASE WHEN datediff(MI,
    LAG(login_time, 1, DATEADD(HH, -3, login_time)) over (Partition by [name] order by [name]
    ,login_time), login_time) > 120 THEN
    1 ELSE 0 END AS NewSession

Note I made the default on the lag fall outside the 2hour rule so the very first login gets a new session "flag". I now get these results

rn	name	login_time			NewSession
1	user1	10:00:00.0000000	1
2	user1	11:59:00.0000000	0
3	user1	12:00:00.0000000	0
4	user1	14:01:00.0000000	1
5	user1	15:00:00.0000000	0
6	user1	15:05:00.0000000	0
7	user1	18:00:00.0000000	1
8	user1	21:01:00.0000000	1

To get the actual session numbers I can do this (I have a CTE called loginlist which produces the results above)

select *
,DENSE_RANK() OVER (PARTITION BY [name] ORDER BY rn) as seshnum
from loginList
where NewSession = 1

So my whole set of code is now

;with loginList as
(
	SELECT 
		ROW_NUMBER() OVER (Partition by [name] order by [name],[login_time]) as rn
		,[name], [login_time]
		,CASE WHEN datediff(MI, 
			LAG(login_time, 1, DATEADD(HH, -3, login_time)) over (Partition by [name] order by [name]
			,login_time), login_time) > 120 THEN
			1 ELSE 0 END AS NewSession
	FROM @new_table
),insertsessionnums as
(
	select *
	,DENSE_RANK() OVER (PARTITION BY [name] ORDER BY rn) as seshnum
	from loginList
	where NewSession = 1
)
update a set sessionnum = isnull(c.seshnum,0)
from @new_table a
inner join loginList b on a.[name] = b.[name] and a.login_time = b.login_time
left outer join insertsessionnums c on b.rn = c.rn;

Which gives results

name	login_time			sessionnum
user1	10:00:00.0000000	1
user1	11:59:00.0000000	0
user1	12:00:00.0000000	0
user1	14:01:00.0000000	2
user1	15:00:00.0000000	0
user1	15:05:00.0000000	0
user1	18:00:00.0000000	3
user1	21:01:00.0000000	4

This now becomes an exercise in filling in the blanks, and that will very much depend on which version of SQL you are running. It's a well-documented exercise - here is one discussion Filling In Missing Values Using the T-SQL Window Frame - Simple Talk[^]