Pairing issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pairing issue

Author

Topic

Simon13579
Starting Member

10 Posts

Posted - 2008-05-12 : 11:30:18

Hello,

I have a table that contains rows which are either a type logon or logoff (this is determined by the column 'Type', which is either 'LogOn' or 'LogOff').

Other columns are LogOnID (an autonumber, primary key), UserID and Time.

I would like to pair log ons with log offs for users in a given day.

I am currently using the following query:

SELECT
l1.UserID, l1.Time AS 'Log On', l2.Time AS 'Log Off'
WHERE
l1.UserID = l2.UserID
AND l1.Type = 'LogOn' AND l2.Type = 'LogOff'
AND l1.Time > '2008-05-12 00:00:00.000' AND l1.Time < '2008-05-12 23:59:59.000'
AND l2.Time > '2008-05-12 00:00:00.000' AND l2.Time < '2008-05-12 23:59:59.000'

However this is flawed, as if a person logs in multiple times, it will pair each log on with each log off.

Is there a way to put them into pairs so that I get a log on and pair it with the log off that has the smallest time difference between it and the log on? So, for instance, if I have a log on at 12:30, it would pair with the log off at 13:30, rather than 13:45, as the 13:30 is the closer one, and so must be the one associated with that log on.

It gets even worse, too, because it is possible to have multiple logins in a row without a log off (so it could go log in, log in, log in, log off, log in, log off, for instance), but it should pair the LAST login with the log off (if you see what I mea, in the instance before, the third log in should be associated with the log off).

It seems like a tall order, and I don't know how to do it myself. Perhaps I could pair them based on the smallest difference between the ID, as this is just an incrementing number. Again, I wouldn't know how to do this.

If anyone could shed any light on how this might be possible I would appreciate it.

Thanks,

Simon

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-12 : 11:39:44

[code]SELECT UserID,
MIN(CASE WHEN Type = 'LogOn' THEN Time ELSE NULL END) AS [Log On],
MAX(CASE WHEN Type = 'LogOff' THEN Time ELSE NULL END) AS [Log Off]
FROM Table1
GROUP BY UserID,
DATEDIFF(DAY, '19000101', Time)
ORDER BY UserID,
DATEDIFF(DAY, '19000101', Time)[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-12 : 11:41:07

If its SQL 2005,you can do like this:-

SELECT t.UserID,b.Time AS Logon,t.Time AS LogOff
FROM Table t
CROSS APPLY (SELECT MAX(time)
             FROM Table
             WHERE UserID=t.UserID
             AND Type='LogOn'
             AND Time < t.Time)b
Where t.Type='LogOff'

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-05-12 : 11:44:33

Or perhaps:


SELECT l1.UserID
       ,max(l1.Time) AS 'Log On'
       ,l2.Time AS 'Log Off'
from   loginlogout l1
join   loginlogout l2
              on l2.UserID = l1.UserID
              and l2.Time > l1.Time
WHERE  l1.Type = 'LogOn' 
AND    l2.Type = 'LogOff'
AND l1.Time > '2008-05-12 00:00:00.000' 
AND l1.Time < '2008-05-12 23:59:59.000'
AND l2.Time > '2008-05-12 00:00:00.000' 
AND l2.Time < '2008-05-12 23:59:59.000'
group by l1.UserID
       ,l2.Time

If none of these do it, post code to create a sample table with INSERTs to provide samle data and expected results.

EDIT:
also post what sql server version you are using ie: 2000, 2005, etc.

Be One with the Optimizer
TG

Simon13579
Starting Member

10 Posts

Posted - 2008-05-13 : 03:54:08

Hi,

Thank you all for replying, I appreciate the help.

Peso, unfortunately your solution doesn't work because a user can have multiple log ins in one day, and I can't seem to modify your query to allow multiple log ins.

Visakh, your query won't run, citing 'No column was specified for column 1 of 'b'. Unfortunately I don't have the SQL knowledge to fix it...

TG, yours almost works, but unfortunately it's not taking into account log ons that don't have a requisite log off (which seems to me like the most difficult part to accomplish).

Please run these queries:


CREATE TABLE LogOn
(
	LogOnID int IDENTITY(1,1) NOT NULL,
	UserID varchar(32),
	Type varchar(6),
	Time datetime
)

INSERT INTO LogOn(UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:30:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('Bob', 'LogOff', '2008-05-12 12:31:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:32:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:33:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('Bob', 'LogOff', '2008-05-12 12:34:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('John', 'LogOn', '2008-05-12 12:30:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('John', 'LogOn', '2008-05-12 12:31:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('John', 'LogOn', '2008-05-12 12:32:00.000')
INSERT INTO LogOn(UserID, Type, Time) VALUES ('John', 'LogOff', '2008-05-12 12:33:00.000')

The result I would hope to see would be:


UserId  LogOn           LogOff
Bob     12:30:00.000    12:31:00.000
Bob     12:32:00.000    NULL
Bob     12:33:00.000    12:34:00.000
John    12:30:00.000    NULL
John    12:31:00.000    NULL
John    12:32:00.000    12:33:00.000

This seems difficult as you have to pair the most recent log on to a log off, rather than any log on that is less than a log off.
It might be possible by having the smallest difference between the LogOnID for the log off and log on, but I don't really know how to go about implementing that...

I am running SQL Server 2005.

Thanks for your continued help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 03:56:11

[code]SELECT t.UserID,t.[Time] AS LogOn,b.MinTime AS Logoff
FROM #LogOn t
OUTER APPLY (SELECT MIN([Time]) AS MinTime
FROM #LogOn
WHERE UserID=t.UserID
AND [Type]='LogOff'
AND LogOnID=t.LogOnID+1
AND [Time] > t.Time)b
Where t.Type='LogOn'

output
--------------------------------

UserID LogOn Logoff
-------------------------------- ----------------------- -----------------------
Bob 2008-05-12 12:30:00.000 2008-05-12 12:31:00.000
Bob 2008-05-12 12:32:00.000 NULL
Bob 2008-05-12 12:33:00.000 2008-05-12 12:34:00.000
John 2008-05-12 12:30:00.000 NULL
John 2008-05-12 12:31:00.000 NULL
John 2008-05-12 12:32:00.000 2008-05-12 12:33:00.000[/code]

Simon13579
Starting Member

10 Posts

Posted - 2008-05-13 : 04:33:58

Visakh. That's incredible.

Unfortunately this doesn't quite work, because the log ons and log offs can be mixed for each employee, so LogOnID=t.LogOnID+1 will not always be true. E.g.,


INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:35:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('John', 'LogOn', '2008-05-12 12:36:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:37:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('John', 'LogOff', '2008-05-12 12:37:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOff', '2008-05-12 12:38:00.000')

This should return:


UserId  LogOn           LogOff
Bob     12:30:00.000    12:31:00.000
Bob     12:32:00.000    NULL
Bob     12:33:00.000    12:34:00.000
John    12:30:00.000    NULL
John    12:31:00.000    NULL
John    12:32:00.000    12:33:00.000
Bob     12:35:00.000    NULL
John    12:36:00.000    12:37:00.000
Bob     12:37:00.000    12:38:00.000

But doesn't. Sorry to be such a pain!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-13 : 05:18:05

quote:

Originally posted by Simon13579

Visakh. That's incredible.

Unfortunately this doesn't quite work, because the log ons and log offs can be mixed for each employee, so LogOnID=t.LogOnID+1 will not always be true. E.g.,


INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:35:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('John', 'LogOn', '2008-05-12 12:36:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOn', '2008-05-12 12:37:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('John', 'LogOff', '2008-05-12 12:37:00.000')
INSERT INTO LogON (UserID, Type, Time) VALUES ('Bob', 'LogOff', '2008-05-12 12:38:00.000')

This should return:


UserId  LogOn           LogOff
Bob     12:30:00.000    12:31:00.000
Bob     12:32:00.000    NULL
Bob     12:33:00.000    12:34:00.000
John    12:30:00.000    NULL
John    12:31:00.000    NULL
John    12:32:00.000    12:33:00.000
Bob     12:35:00.000    NULL
John    12:36:00.000    12:37:00.000
Bob     12:37:00.000    12:38:00.000

But doesn't. Sorry to be such a pain!

No probs. Slight modification:-

SELECT t.UserID,t.[Time] AS LogOn,
CASE WHEN DATEDIFF(ms,b.MinTime,ISNULL(c.MinTime,b.MinTime)) >=0 THEN b.MinTime END AS Logoff
FROM #LogOn t
OUTER APPLY (SELECT MIN([Time]) AS MinTime
             FROM #LogOn
             WHERE UserID=t.UserID
             AND [Type]='LogOff'
            AND LogOnID>t.LogOnID)b
OUTER APPLY (SELECT MIN([Time]) AS MinTime
             FROM #LogOn
             WHERE UserID=t.UserID
             AND [Type]='LogOn'
            AND LogOnID>t.LogOnID)c
Where t.Type='LogOn'

output
---------------------------------------------------------------------
UserID                           LogOn                   Logoff
-------------------------------- ----------------------- -----------------------
Bob                              2008-05-12 12:30:00.000 2008-05-12 12:31:00.000
Bob                              2008-05-12 12:32:00.000 NULL
Bob                              2008-05-12 12:33:00.000 2008-05-12 12:34:00.000
John                             2008-05-12 12:30:00.000 NULL
John                             2008-05-12 12:31:00.000 NULL
John                             2008-05-12 12:32:00.000 2008-05-12 12:33:00.000
Bob                              2008-05-12 12:35:00.000 NULL
John                             2008-05-12 12:36:00.000 2008-05-12 12:37:00.000
Bob                              2008-05-12 12:37:00.000 2008-05-12 12:38:00.000

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 05:20:12

[code]DECLARE @Sample TABLE (UserID VARCHAR(32), Type VARCHAR(6), Time DATETIME)

INSERT @Sample
SELECT 'Bob', 'LogOn', '2008-05-12 12:30:00.000' UNION ALL
SELECT 'Bob', 'LogOff', '2008-05-12 12:31:00.000' UNION ALL
SELECT 'Bob', 'LogOn', '2008-05-12 12:32:00.000' UNION ALL
SELECT 'Bob', 'LogOn', '2008-05-12 12:33:00.000' UNION ALL
SELECT 'Bob', 'LogOff', '2008-05-12 12:34:00.000' UNION ALL
SELECT 'John', 'LogOn', '2008-05-12 12:30:00.000' UNION ALL
SELECT 'John', 'LogOn', '2008-05-12 12:31:00.000' UNION ALL
SELECT 'John', 'LogOn', '2008-05-12 12:32:00.000' UNION ALL
SELECT 'John', 'LogOff', '2008-05-12 12:33:00.000' UNION ALL
SELECT 'Bob', 'LogOn', '2008-05-12 12:35:00.000' UNION ALL
SELECT 'John', 'LogOn', '2008-05-12 12:36:00.000' UNION ALL
SELECT 'Bob', 'LogOn', '2008-05-12 12:37:00.000' UNION ALL
SELECT 'John', 'LogOff', '2008-05-12 12:37:00.000' UNION ALL
SELECT 'Bob', 'LogOff', '2008-05-12 12:38:00.000'

SELECT UserID,
LogOn,
LogOff
FROM (
SELECT i.UserID,
i.Time AS LogOn,
CASE ROW_NUMBER() OVER (PARTITION BY i.UserID, o.Time ORDER BY i.Time DESC)
WHEN 1 THEN o.Time
ELSE NULL
END AS LogOff,
ROW_NUMBER() OVER (PARTITION BY i.UserID, i.Time ORDER BY o.Time) AS RecID
FROM @Sample AS i
LEFT JOIN @Sample AS o ON o.UserID = i.UserID
AND o.Type = 'LogOff'
AND o.Time > i.Time
WHERE i.Type = 'LogOn'
) AS d
WHERE RecID = 1
ORDER BY UserID,
LogOn[/code]

E 12°55'05.25"
N 56°04'39.16"

Simon13579
Starting Member

10 Posts

Posted - 2008-05-13 : 05:54:46

Visakh and Peso, that is quite extraordinary. Now I'm spoiled for choice!

Thank you both so much; you help is greatly appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:02:04

You should be aware that there are some differencies in the algorithms.
Visakh16 rely on records inserted in correct order (hence the use of LogonID).
For the supplied small sample set, Visakhs16 suggestion needs 57 reads.

My suggestion does not rely on LogonID at all, only the actual times.
For the supplied small sample set, my suggestion needs 30 reads.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:05:55

Using SET STATISTICS IO {ON | OFF } revealed following


Peso
Table '#4D94879B'. Scan count 2, logical reads 10, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Visakh16
Table '#4D94879B'. Scan count 19, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

My suggestion will in theory scale exponential with n^2.
Visakhs16 suggestion will in theory scale exponential with n^3

This will have a real impact for a larger set of sample data.

E 12°55'05.25"
N 56°04'39.16"

Simon13579
Starting Member

10 Posts

Posted - 2008-05-13 : 06:21:23

Wow, Peso, that's impressive stuff; your SQL knowlege is incredible. It seems sensible to opt for your solution.

Again, thanks both of you for your help.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-13 : 06:25:31

To make a decision, you should try both suggestion on your real data in your environment.
That's the only way for sure to know which will perform better on a live sized "date pairs".

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources