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?
 
pcelbaConnect With a Mentor Commented:
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
 
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
 
Govinda2020Author Commented:
Thanks a lot....That was perfect answer.....
0
All Courses

From novice to tech pro — start learning today.