CODEWITHSUNDEEP
sql-servert-sql
luisdev
luisdev

Reputation: 568

Easier way to count users in T-SQL

I'm using this query in SQL Server 2016 to determine how many users have logged into my system.

The users.lastaccess column contains a unix timestamp, so I use DATEDIFF() to convert it to a yyyy-mm-dd hh:mm:ss date.

SELECT 
    COUNT(*) AS user_logins
FROM
    (SELECT 
         ROW_NUMBER() OVER(ORDER BY lastaccess DESC) AS Row
     FROM 
         users
     WHERE 
         lastaccess > DATEDIFF(s, '1970-01-01 02:00:00', (SELECT Convert(DateTime, DATEDIFF(DAY, 0, GETDATE()))))
)

The result is a simple number, e.g. 75, representing the number of users who have been authenticated on the system.

Upvotes: 0

Views: 295

Answers (2)

HABO
HABO

Reputation: 15816

The following code returns the count of users. It uses cast to drop the time-of-day from the value returned by GetDate and uses ISO 8601 for the base date/time of the unix system.

select Count(*) as User_Logins
  from Users
  where LastAccess > DateDiff( s, '1970-01-01T02:00:00', Cast( GetDate() as Date ) );

Upvotes: 2

marc_s
marc_s

Reputation: 754408

Why do you need a correlated subquery and a ROW_NUMBER() windowing function at all? And what is that oddball date-based WHERE clause? What are you really checking for - the fact that last_access is not null/empty??

Just use:

SELECT 
    COUNT(*) AS user_logins
FROM
    dbo.users
WHERE 
    -- your WHERE condition isn't very clear - please add code as needed
    -- but *DO NOT* convert dates to string to compare! Compare proper dates!
    lastaccess IS NOT NULL

Also: if you have a non-nullable, narrow, fixed-width column in your dbo.Users table, you should have a nonclustered index on this (e.g. on lastaccess - is that column nullable?) - that could speed things up quite a bit

Upvotes: 2

Related Questions