Finding top user count for each PC

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

SQL Server 2000 Forums

Transact-SQL (2000)

Finding top user count for each PC

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-31 : 08:19:57

Brandon writes "I have a table (User_Logons) with the following fields:

ComputerName
UserID
EmailID
NumberofLogons
LastLogonTimeKey

I need to list each distinct computer name-and it's owner information. The owner is the UserID with the most number of logons for that COmputerName. In the case of a tie-the latest LastLogonTimeKey wins and is the owner.

I just need to return the ComputerName, UserID, and EmailID.

Thanks!"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-31 : 11:58:29

haven't tried this but see if it works for you:

select computername, userID, emailID
from brandon b
where not exists (
select 1
from brandon
where computername = b.computername
and userID != b.userID
and ( numberoflogons > b.numberoflogons or (
numberoflogons = b.numberoflogons and lastlogontimekey > b.lastlogontimekey )

Jonathan Boott, MCDBA
{0}

brandonl
Yak Posting Veteran

58 Posts

Posted - 2002-07-31 : 12:38:16

Had to add a couple of "))"'s to the end, but I found a problem where some userid's are lower case-some uppercase, (if a user logs in with uppercase letters it updates the login count, same for uppercase-thus adding two records {USER1, user1}).

I can't change the data, however, the program for inserting the data has been fixed. But, there's still older records in the database. As it was duplicating some computernames.

I tried this instead-and it seems to work. See anything wrong with it?

SELECT DISTINCT Computer_Name00 AS computername, UPPER(User_Name00) AS Username, Email_ID00 AS emailID
FROM logondata b
WHERE (NOT EXISTS
(SELECT 1
FROM logondata
WHERE computer_Name00 = b.computer_Name00 AND upper(User_Name00) != upper(b.User_Name00) AND
(Number_Of_Logons00 > b.Number_Of_Logons00 OR
(Number_Of_Logons00 = b.Number_Of_Logons00 AND Time_Stamp00 > b.Time_Stamp00))))

~BrandonL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources