Solved

SQL query on SQL server 2005

Posted on 2009-07-02
4
196 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 76
ms sql + top 1 for each customer 3 70
denied execute as 13 57
What Is an Error? 2 59
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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