Solved

SQL query on SQL server 2005

Posted on 2009-07-02
4
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 42

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 42

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 71
Help with stripping out character in SQL LEFT/RIGHT/REPLACE 2 49
Replace Dates in query 14 55
Find results from sql within a time span 11 55
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard 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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

740 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