Click here to Skip to main content
14,768,080 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two columns in my table, name, and login_time. I am looking at how to add a session column if another login was made within 2 hours following a prior login, it considered the same login session, otherwise, it is a new session.

Login session should be partitioned by name.

What I have tried:

SELECT name, login_time,
       DENSE_RANK() OVER (PARTITION BY name ORDER BY login_time) usage_days
FROM new_table
ORDER BY name, login_time;
Posted
Updated 4 days ago
Comments
CHill60 4 days ago
   
The body of your post doesn't appear to have anything to do with the title of your post. Perhaps if you share some sample data and your expected results we might be able to help

1 solution

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[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900