CODEWITHSUNDEEP
sql-server-2008t-sql
Mick Walker
Mick Walker

Reputation: 3847

Custom Report on User Data - SQL Server

Given the following very simple table:

Create Table tblUserLogins (
    LoginNumber int PRIMARY KEY IDENTITY(1,1),
    Username varchar(100),
    LoginTime datetime
)

Basically when a user logs into the site, a record is created in this table, indicating the user logged in. (For security reasons the developers in my team do not have access to the tables holding the login information, so this was a work-around).

What I need, is some help writing a query which will actually return me the username of the user who logged on the most during a given period (supplied as input values to the procedure).

I can select the data between any 2 given dates using the following:

SELECT * FROM tblUserLogins 
WHERE LoginTime BETWEEN @DateFrom AND @DateTo

However I am not sure how I can aggregate the user data, without first dumping the contents of the above query to a temporary table.

Any help would be gratefully received.

Upvotes: 2

Views: 108

Answers (2)

aF.
aF.

Reputation: 66697

Here it is a full example:

Create Table #tblUserLogins (
    LoginNumber int PRIMARY KEY IDENTITY(1,1),
    Username varchar(100),
    LoginTime datetime
)

declare @DateFrom datetime, @DateTo datetime

select @DateFrom = getdate()

insert into #tblUserLogins values ('test1', getdate())
insert into #tblUserLogins values ('test2', getdate())
insert into #tblUserLogins values ('test1', getdate())


select @DateTo = getdate()

SELECT TOP 1 Username, count(LoginTime) 'Total' FROM #tblUserLogins
WHERE LoginTime BETWEEN @DateFrom AND @DateTo
GROUP BY UserName
ORDER BY Total, Username desc

drop table #tblUserLogins

If there are more than one username with the same number of entries, it'll only show one (with the name most near z).

Upvotes: 0

benni_mac_b
benni_mac_b

Reputation: 8877

SELECT TOP 1 
 Username,
 COUNT(Username) as Total
FROM 
 tblUserLogins
WHERE 
 LoginTime BETWEEN @DateFrom AND @DateTo
GROUP BY
 Username
ORDER BY
 Total DESC

Upvotes: 1

Related Questions