leo.bioeng

Comments (2)

Followers (19)

Reputation 260

Page:

Comment hidden because of low score. Click to expand.

of 8 vote

This following solution uses recursion, so that it can properly track a user that connects multiple times, every time being within the 30-min window from the last login.

This is the schema and data I'm using:

CREATE TABLE input (userid tinyint, logintime time);
INSERT INTO input VALUES (1, '9:00am');
INSERT INTO input VALUES (2, '9:10am');
INSERT INTO input VALUES (1, '9:25am');
INSERT INTO input VALUES (30, '12:34pm');
INSERT INTO input VALUES (23, '3:00am');
INSERT INTO input VALUES (1, '9:45am');
INSERT INTO input VALUES (1, '11:45am');

CREATE VIEW input2 AS SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input;

(I'm only using the view for clarity; see at the end for the final query that does not require additional commands).

Notice that there are three chained logins for userid 1: 9:00, 9:25 and 9:45. The following query will properly assign the IDs:

WITH CTE (userid, logintime, previousEventTime, currentLevel) AS
(SELECT userid, logintime, logintime, 1 FROM input2 WHERE rownumber=1
UNION ALL
SELECT a.userid, a.logintime, 
CASE 
   WHEN datediff(mi, b.logintime, a.logintime) < 30 THEN b.previousEventTime
   ELSE a.logintime
END,
b.currentLevel+1
FROM input2 as a INNER JOIN CTE as b ON a.userid = b.userid WHERE rownumber = b.currentLevel+1)
SELECT userid, logintime, DENSE_RANK() OVER (ORDER BY previousEventTime) as SessionID FROM CTE;

Result table:

userid	logintime	        SessionID
23	    03:00:00.0000000	1
1	    09:00:00.0000000	2
1	    09:25:00.0000000	2
1	    09:45:00.0000000	2
2	    09:10:00.0000000	3
1	    11:45:00.0000000	4
30	    12:34:00.0000000	5

The stand alone query (no view required) is as follows:

WITH CTE (userid, logintime, previousEventTime, currentLevel) AS
(SELECT userid, logintime, logintime, 1 FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input) c WHERE rownumber=1
UNION ALL
SELECT a.userid, a.logintime, 
CASE 
   WHEN datediff(mi, b.logintime, a.logintime) < 30 THEN b.previousEventTime
   ELSE a.logintime
END,
b.currentLevel+1
FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY userid ORDER BY logintime) as rownumber FROM input) as a INNER JOIN CTE as b ON a.userid = b.userid WHERE rownumber = b.currentLevel+1)
SELECT userid, logintime, DENSE_RANK() OVER (ORDER BY previousEventTime) as SessionID FROM CTE;

- leo.bioeng December 15, 2017 | Flag Reply

Comment hidden because of low score. Click to expand.

of 0 vote

SQL Server only answer - the WITH TIES allows for all the records matching highest invoice amount (I understand this solution is not generic, but for shake of completeness):

SELECT TOP(1) WITH TIES * FROM Invoice WHERE year(inv_date) = 2016 ORDER BY inv_amt DESC

- leo.bioeng December 14, 2017 | Flag Reply

Page:

0
Rep
ConnieLavender, Animator at Altera

My name is ConnieLavender . I am working as a Broker associate . I love field work and visiting different places to ...
0
Rep
kristenjjimenezk, Analyst at ADP

We Statistical assistants establish and check numerical facts in many different areas of business, government, and industry. Right now I ...
0
Rep
seffreyclifford, Area Sales Manager at Achieve Internet

I am a self-motivated office administrator. I am responsible for providing administrative support to office personnel. I have excellent written ...
0
Rep
NirvedPerez, abc at 8x8

To make sure a thorough investigation is done if discrepancies occur. Execute the Brand Customer Service standards to meet or ...
0
Rep
limachiya788, Associate at ABC TECH SUPPORT

LeviWebber , is a Housekeeping cleaner at Exact Solutions . I am also exploring new things . Mayong Assam tantrik contact number . Housekeepers ...
0
Rep
johnsantana9ytt9, Backend Developer at Accolite software

I am working as a Support service manager at Pro Star Garden Management . I had a different experience while working ...
0
Rep
AahnaAllen, AT&T Customer service email at A9

I am a multilingual Judge with 5 years of combined experience in presiding over court proceedings, prosecuting cases, and tirelessly ...
0
Rep
cloptonchirsty, Android Engineer at 8x8

Hello, I am an Organic chemist and my all study and degree is complete from New York.Apart from this ...
0
Rep
LaylaMoore, Analyst at ADP

I am a skilled content editor with 3 years of excellent service in the communications field. I developed, researched, and ...
0
Rep
marksloan761, Backend Developer at Apache Design

As a Meeting manager at Red Bears Tavern here I am working for approximately ten years . In my daily life ...
0
Rep
RilynFreeman, Area Sales Manager at Knewton

Hi my name is Rilyn. Library assistant in Jeans Unlimited company. I help to keep libraries organised and efficient while ...
0
Rep
KarlOlson, job tessio at design

I am Karl .Transportation ticket agent, I help plan travel itineraries by suggesting local tourist attractions and places of interest ...
0
Rep
CamilaHill, Accountant at ADP

I am a performance-driven leader providing high-level administrative and operational support, coordinating schedules, preparing travel and documents, and exploring Surah ...
10
Rep
NancyCramer, Financial Software Developer at Abs india pvt. ltd.

Nancy , an administrative assistant at Libera Inc. to leverage organizational and research skills to support internal and external prayer to ...
0
Rep
ggroverwmiller, Android Engineer at 247quickbookshelp

Hello I am a Budget officer with 5 years of experience. The Budget Officer implements budgeting and financial record keeping ...
0
Rep
YuvaanMoore, abc at 8x8

I have extensive experience as a newspaper reporter, television news reporter, AM radio talk show host and anchorman for major ...
0
Rep
OishiGreen, Android test engineer at ABC TECH SUPPORT

I am a results-oriented debt collector with 3 years of experience and knowledge in the leasing/finance sector. I have ...
0
Rep
JulianMiller, abc at ABC TECH SUPPORT

I am a professional auditing assistant with extensive experience in handling administrative duties and executive responsibilities associated with both internal ...
0
Rep
MairaSmith, Integration Software Engineer at Absolute Softech Ltd

MairaSmith , an Quality Control Assistant with a record of success in developing and implementing new quality control policies and procedures ...

CareerCup

leo.bioeng

Books

Videos

Resume Review

Mock Interviews