SQL query on SQL server 2005

I have to query the database to find the peak 5 mins in a day, where highest number of users are logged in . I have userID and date on the same table.
Govinda2020Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pcelbaCommented:
Hope you have UserID and DateTIME. Do you also have the action on that time? Hopefully yes.

Then you should clarify if you would like to split each hour to 12 parts with given beginning and end time OR if you really need to select the highest 5 minutes daily peak without given fixed time fragments. Another question is how to count same user logged-in on more computers.
0
Govinda2020Author Commented:
Thanks for getting back, Yeah we have action = logged in, we can assume that there is only one user logged-in .Yeah its better to split each hour to 12 parts with given beginning and end time.
0
pcelbaCommented:
If you have just one action (logged in) then you are not able to count logged in users because you don't know who is logged already out... In such case the maximum is always the last 5 minutes :-). But you can use your data to count the 5 minutes peak of login operation itself which is different from number of logged in users.

It would be better if you disclose your data... or their structure at least.

The following example selects the 5 minutes peak of login operations from my table (it does not split hour to 12 portions):

;WITH CTE (RowNo, DateTime, cnt) AS
(
SELECT a.RowNo, a.Timestamp, COUNT(b.UserId) cnt
  FROM (SELECT ROW_NUMBER() OVER(ORDER BY Timestamp) RowNo, UserId, Timestamp FROM tt) a
 INNER JOIN (SELECT ROW_NUMBER() OVER(ORDER BY Timestamp) RowNo, UserId, Timestamp FROM tt) b
    ON b.RowNo >= a.RowNo AND b.Timestamp <= DATEADD(minute, 5, a.Timestamp)
 group by a.RowNo, a.Timestamp
)
SELECT DateTime, cnt
  FROM CTE
 WHERE cnt = (SELECT MAX(cnt) FROM CTE)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Govinda2020Author Commented:
Thanks a lot....That was perfect answer.....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.