Solved

SQL query on SQL server 2005

Posted on 2009-07-02
4
184 Views
Last Modified: 2012-05-07
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.
0
Comment
Question by:Govinda2020
  • 2
  • 2
4 Comments
 
LVL 41

Expert Comment

by:pcelba
ID: 24761985
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
 

Author Comment

by:Govinda2020
ID: 24762969
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
 
LVL 41

Accepted Solution

by:
pcelba earned 500 total points
ID: 24767545
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
 

Author Closing Comment

by:Govinda2020
ID: 31599102
Thanks a lot....That was perfect answer.....
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now