September 26, 2008 at 1:34 am
Hi All,
I am having a requirement to display the top 5 recently visited users for a web site.
I used the following query:
create procedure [dbo].[getRecentUsers](@ClientId bigint)
as
begin
select distinct top 5 U.UserName,C.ClientId,Ua.UId as UserId,convert(varchar,ReportDate,101) as ReportDate from Clients C,UserActivityLog Ua, Users U
where U.ClientId=C.ClientId and C.ClientId=@ClientId and U.UId=Ua.UId and Ua.EventId=14
order by ReportDate desc
end
But the result i am getting is Usernames who visited the site two, three days back with same names.
i.e.,
if a use with name 'shravan' has logged into the system today,yesterday and day-before-yesterday, it is displaying the name shravan three times in the table.
I need the name shravan only when he recently logged in. The remaining should not be shown.
Did you get through the problem?
Thanks in advance for any help provided.
Regards
Mahathi.
September 26, 2008 at 1:46 am
Which table stores user login information?
September 26, 2008 at 1:52 am
The UserActivityLog table stores the user login infromation and the schema of the table is:
CREATE TABLE [dbo].[UserActivityLog](
[RId] [int] IDENTITY(1,1) NOT NULL,
[ClientId] [bigint] NULL,
[UId] [int] NULL,
[Operation] [nvarchar](15) NULL,
[ReportDesc] [nvarchar](200) NULL,
[ReportDate] [datetime] NULL,
[Status] [int] NULL,
[EventId] [int] NULL,
CONSTRAINT [PK__Reports__42E1EEFE] PRIMARY KEY CLUSTERED
(
[RId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[UserActivityLog] WITH CHECK ADD CONSTRAINT [FK__UserActiv__Event__59063A47] FOREIGN KEY([EventId])
REFERENCES [dbo].[Events] ([EventId])
GO
ALTER TABLE [dbo].[UserActivityLog] CHECK CONSTRAINT [FK__UserActiv__Event__59063A47]
GO
ALTER TABLE [dbo].[UserActivityLog] WITH CHECK ADD CONSTRAINT [FK_UserActivityLog_UserActivityLog] FOREIGN KEY([RId])
REFERENCES [dbo].[UserActivityLog] ([RId])
GO
ALTER TABLE [dbo].[UserActivityLog] CHECK CONSTRAINT [FK_UserActivityLog_UserActivityLog]
September 26, 2008 at 1:52 am
The eventId 14 is the eventid fro login
September 26, 2008 at 1:59 am
The reason "shravan" is being returned is becoz he has visited the site during the past 3 days. You are taking distinct of 5 columns - U.UserName,C.ClientId,Ua.UId as UserId,convert(varchar,ReportDate,101) .
"Keep Trying"
September 26, 2008 at 2:00 am
SELECT TOP 5userName,
clientID,
userID,
CONVERT(CHAR(10), reportDate, 101) AS reportDate
FROM(
SELECTu.userName,
c.clientID,
ua.uID AS userID,
ua.reportDate,
ROW_NUMBER() OVER (PARTITION BY u.uID ORDER BY ua.reportDate DESC) AS RecID
FROMClients AS c
INNER JOINUsers AS u ON u.clientID = c.clientID
INNER JOINUserActivityLog AS ua ON ua.uID = u.uID
AND ua.eventID = 14
WHEREc.clientID = @clientID
) AS w
WHERERecID = 1
ORDER BYreportDate DESC
N 56°04'39.16"
E 12°55'05.25"
September 26, 2008 at 2:03 am
Should i apply the distinct keyword only for th user name. Even I tried that query. But I got the same result.
September 26, 2008 at 2:23 am
Hi Peso,
Thank you for the query. It is working great.
Regards
Mahathi.
September 26, 2008 at 2:28 am
one problem is, if a user logins today and if loggins once again, it is not being displayed on the top position. It is displayed in the previous place itself.
Regards
Mahathi.
September 26, 2008 at 5:27 am
SELECT TOP 5 w.userName,
@clientID,
w.uID AS userID,
CONVERT(CHAR(10), w.rd, 101) AS reportDate
FROM (
SELECT u.userName,
u.uID,
ua.reportDate AS rd,
ROW_NUMBER() OVER (PARTITION BY u.uID ORDER BY ua.reportDate DESC) AS RecID
FROM Users AS u
INNER JOIN UserActivityLog AS ua ON ua.uID = u.uID
AND ua.eventID = 14
WHERE u.clientID = @clientID
) AS w
WHERE w.RecID = 1
ORDER BY w.rd DESC
N 56°04'39.16"
E 12°55'05.25"
September 30, 2008 at 1:41 am
Hi Peso,
Sorry the reply was late.Thank you. Its working fine.
Regards
Mahathi.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply