?
Solved

Develop query to extract minimum response time and its date stamp, maximum response time and its date stamp

Posted on 2014-12-11
13
Medium Priority
?
108 Views
Last Modified: 2014-12-17
Need assistance in developing a query to extract minimum response time and its date stamp, maximum response time and its date stamp from sample data below. Ideally when the Response Time is greater than 0 then drops to 0 again.

Ideal output would be:
DEVICE          MIN DATETIME       MIN               MAX DATETIME      MAX
ABCDEF        12/10/2014 17:06     875.0 ms      12/10/2014 16:11      917.0 ms
ABCDEF        12/11/2014 11:26     847.0 ms      12/11/2014 9:31        943.0 ms



Sample data (I out in the '<<<MIN', '<<<MAX' for easier identification)
DATE / TIME       Response Time        
12/10/2014 13:36      unknown       
12/10/2014 13:41      unknown       
12/10/2014 13:46      unknown       
12/10/2014 13:51      unknown       
12/10/2014 13:56      unknown       
12/10/2014 14:01      unknown       
12/10/2014 14:06      unknown       
12/10/2014 14:11      unknown       
12/10/2014 14:16      unknown       
12/10/2014 14:21      unknown       
12/10/2014 14:26      unknown       
12/10/2014 14:31      unknown       
12/10/2014 14:36      unknown       
12/10/2014 14:41      unknown       
12/10/2014 14:46      unknown       
12/10/2014 14:51      unknown       
12/10/2014 14:56      unknown       
12/10/2014 15:01      unknown       
12/10/2014 15:06      unknown       
12/10/2014 15:11      899.0 ms       
12/10/2014 15:16      896.0 ms       
12/10/2014 15:21      888.0 ms       
12/10/2014 15:26      890.0 ms       
12/10/2014 15:31      895.0 ms       
12/10/2014 15:36      894.0 ms       
12/10/2014 15:41      887.0 ms       
12/10/2014 15:46      888.0 ms       
12/10/2014 15:51      895.0 ms       
12/10/2014 15:56      897.0 ms       
12/10/2014 16:01      903.0 ms       
12/10/2014 16:06      902.0 ms       
12/10/2014 16:11      917.0 ms       <<<MAX
12/10/2014 16:16      896.0 ms       
12/10/2014 16:21      879.0 ms       
12/10/2014 16:26      882.0 ms       
12/10/2014 16:31      892.0 ms       
12/10/2014 16:36      897.0 ms       
12/10/2014 16:41      900.0 ms       
12/10/2014 16:46      902.0 ms       
12/10/2014 16:51      909.0 ms       
12/10/2014 16:56      887.0 ms       
12/10/2014 17:01      899.0 ms       
12/10/2014 17:06      875.0 ms       <<<MIN
12/10/2014 17:11      unknown       
12/10/2014 17:16      unknown       
12/10/2014 17:21      unknown       
12/10/2014 17:26      unknown       
12/10/2014 17:31      unknown       
12/10/2014 17:36      unknown       
12/10/2014 17:41      unknown       
12/10/2014 17:46      unknown       
12/10/2014 17:51      unknown       
12/10/2014 17:56      unknown       
12/10/2014 18:01      unknown       
12/10/2014 18:06      unknown       
12/10/2014 18:11      unknown       
12/10/2014 18:16      unknown       
12/10/2014 18:21      unknown       
12/10/2014 18:26      unknown       
12/10/2014 18:31      unknown       
12/10/2014 18:36      unknown       
12/10/2014 18:41      unknown       
12/10/2014 18:46      unknown       
12/10/2014 18:51      unknown       
12/10/2014 18:56      unknown       
12/10/2014 19:01      unknown       
12/10/2014 19:06      unknown       
12/10/2014 19:11      unknown       
12/10/2014 19:16      unknown       
12/10/2014 19:21      unknown       
12/10/2014 19:26      unknown       
12/10/2014 19:31      unknown       
12/10/2014 19:36      unknown       
12/10/2014 19:41      unknown       
12/10/2014 19:46      unknown       
12/10/2014 19:51      unknown       
12/10/2014 19:56      unknown       
12/10/2014 20:01      unknown       
12/10/2014 20:06      unknown       
12/10/2014 20:11      unknown       
12/10/2014 20:16      unknown       
12/10/2014 20:21      unknown       
12/10/2014 20:26      unknown       
12/10/2014 20:31      unknown       
12/10/2014 20:36      unknown       
12/10/2014 20:41      unknown       
12/10/2014 20:46      unknown       
12/10/2014 20:51      unknown       
12/10/2014 20:56      unknown       
12/10/2014 21:01      unknown       
12/10/2014 21:06      unknown       
12/10/2014 21:11      unknown       
12/10/2014 21:16      unknown       
12/10/2014 21:21      unknown       
12/10/2014 21:26      unknown       
12/10/2014 21:31      unknown       
12/10/2014 21:36      unknown       
12/10/2014 21:41      unknown       
12/10/2014 21:46      unknown       
12/10/2014 21:51      unknown       
12/10/2014 21:56      unknown       
12/10/2014 22:01      unknown       
12/10/2014 22:06      unknown       
12/10/2014 22:11      unknown       
12/10/2014 22:16      unknown       
12/10/2014 22:21      unknown       
12/10/2014 22:26      unknown       
12/10/2014 22:31      unknown       
12/10/2014 22:36      unknown       
12/10/2014 22:41      unknown       
12/10/2014 22:46      unknown       
12/10/2014 22:51      unknown       
12/10/2014 22:56      unknown       
12/10/2014 23:01      unknown       
12/10/2014 23:06      unknown       
12/10/2014 23:11      unknown       
12/10/2014 23:16      unknown       
12/10/2014 23:21      unknown       
12/10/2014 23:26      unknown       
12/10/2014 23:31      unknown       
12/10/2014 23:36      unknown       
12/10/2014 23:41      unknown       
12/10/2014 23:46      unknown       
12/10/2014 23:51      unknown       
12/10/2014 23:56      unknown       
12/11/2014 0:01      unknown       
12/11/2014 0:06      unknown       
12/11/2014 0:11      unknown       
12/11/2014 0:16      unknown       
12/11/2014 0:21      unknown       
12/11/2014 0:26      unknown       
12/11/2014 0:31      unknown       
12/11/2014 0:36      unknown       
12/11/2014 0:41      unknown       
12/11/2014 0:46      unknown       
12/11/2014 0:51      unknown       
12/11/2014 0:56      unknown       
12/11/2014 1:01      unknown       
12/11/2014 1:06      unknown       
12/11/2014 1:11      unknown       
12/11/2014 1:16      unknown       
12/11/2014 1:21      unknown       
12/11/2014 1:26      unknown       
12/11/2014 1:31      unknown       
12/11/2014 1:36      unknown       
12/11/2014 1:41      unknown       
12/11/2014 1:46      unknown       
12/11/2014 1:51      unknown       
12/11/2014 1:56      unknown       
12/11/2014 2:01      unknown       
12/11/2014 2:06      unknown       
12/11/2014 2:11      unknown       
12/11/2014 2:16      unknown       
12/11/2014 2:21      unknown       
12/11/2014 2:26      unknown       
12/11/2014 2:31      unknown       
12/11/2014 2:36      unknown       
12/11/2014 2:41      unknown       
12/11/2014 2:46      unknown       
12/11/2014 2:51      unknown       
12/11/2014 2:56      unknown       
12/11/2014 3:01      unknown       
12/11/2014 3:06      unknown       
12/11/2014 3:11      unknown       
12/11/2014 3:16      unknown       
12/11/2014 3:21      unknown       
12/11/2014 3:26      unknown       
12/11/2014 3:31      unknown       
12/11/2014 3:36      unknown       
12/11/2014 3:41      unknown       
12/11/2014 3:46      unknown       
12/11/2014 3:51      unknown       
12/11/2014 3:56      unknown       
12/11/2014 4:01      unknown       
12/11/2014 4:06      unknown       
12/11/2014 4:11      unknown       
12/11/2014 4:16      unknown       
12/11/2014 4:21      unknown       
12/11/2014 4:26      unknown       
12/11/2014 4:31      unknown       
12/11/2014 4:36      unknown       
12/11/2014 4:41      unknown       
12/11/2014 4:46      unknown       
12/11/2014 4:51      unknown       
12/11/2014 4:56      unknown       
12/11/2014 5:01      unknown       
12/11/2014 5:06      unknown       
12/11/2014 5:11      unknown       
12/11/2014 5:16      unknown       
12/11/2014 5:21      unknown       
12/11/2014 5:26      unknown       
12/11/2014 5:31      unknown       
12/11/2014 5:36      unknown       
12/11/2014 5:41      unknown       
12/11/2014 5:46      unknown       
12/11/2014 5:51      unknown       
12/11/2014 5:56      unknown       
12/11/2014 6:01      unknown       
12/11/2014 6:06      unknown       
12/11/2014 6:11      unknown       
12/11/2014 6:16      unknown       
12/11/2014 6:21      unknown       
12/11/2014 6:26      unknown       
12/11/2014 6:31      unknown       
12/11/2014 6:36      unknown       
12/11/2014 6:41      unknown       
12/11/2014 6:46      unknown       
12/11/2014 6:51      unknown       
12/11/2014 6:56      unknown       
12/11/2014 7:01      unknown       
12/11/2014 7:06      unknown       
12/11/2014 7:11      unknown       
12/11/2014 7:16      unknown       
12/11/2014 7:21      unknown       
12/11/2014 7:26      unknown       
12/11/2014 7:31      unknown       
12/11/2014 7:36      unknown       
12/11/2014 7:41      unknown       
12/11/2014 7:46      unknown       
12/11/2014 7:51      889.0 ms       
12/11/2014 7:56      889.0 ms       
12/11/2014 8:01      889.0 ms       
12/11/2014 8:06      902.0 ms       
12/11/2014 8:11      894.0 ms       
12/11/2014 8:16      875.0 ms       
12/11/2014 8:21      873.0 ms       
12/11/2014 8:26      878.0 ms       
12/11/2014 8:31      887.0 ms       
12/11/2014 8:36      906.0 ms       
12/11/2014 8:41      883.0 ms       
12/11/2014 8:46      886.0 ms       
12/11/2014 8:51      901.0 ms       
12/11/2014 8:56      886.0 ms       
12/11/2014 9:01      884.0 ms       
12/11/2014 9:06      880.0 ms       
12/11/2014 9:11      875.0 ms       
12/11/2014 9:16      856.0 ms       
12/11/2014 9:21      898.0 ms       
12/11/2014 9:26      857.0 ms       
12/11/2014 9:31      943.0 ms       <<<MAX
12/11/2014 9:36      918.0 ms       
12/11/2014 9:41      871.0 ms       
12/11/2014 9:46      876.0 ms       
12/11/2014 9:51      875.0 ms       
12/11/2014 9:56      891.0 ms       
12/11/2014 10:01      879.0 ms       
12/11/2014 10:06      877.0 ms       
12/11/2014 10:11      861.0 ms       
12/11/2014 10:16      900.0 ms       
12/11/2014 10:21      893.0 ms       
12/11/2014 10:26      885.0 ms       
12/11/2014 10:31      894.0 ms       
12/11/2014 10:36      890.0 ms       
12/11/2014 10:41      884.0 ms       
12/11/2014 10:46      870.0 ms       
12/11/2014 10:51      889.0 ms       
12/11/2014 10:56      883.0 ms       
12/11/2014 11:01      865.0 ms       
12/11/2014 11:06      888.0 ms       
12/11/2014 11:11      896.0 ms       
12/11/2014 11:16      854.0 ms       
12/11/2014 11:21      868.0 ms       
12/11/2014 11:26      847.0 ms       <<<MIN
12/11/2014 11:31      884.0 ms       
12/11/2014 11:36      875.0 ms       
12/11/2014 11:41      890.0 ms       
12/11/2014 11:46      878.0 ms       
12/11/2014 11:51      893.0 ms       
12/11/2014 11:56      unknown       
12/11/2014 12:01      unknown       
12/11/2014 12:06      unknown       
12/11/2014 12:11      unknown       
12/11/2014 12:16      unknown       
12/11/2014 12:21      unknown       
12/11/2014 12:26      unknown       
12/11/2014 12:31      unknown       
12/11/2014 12:36      unknown       
12/11/2014 12:41      unknown       
12/11/2014 12:46      unknown       
12/11/2014 12:51      unknown       
12/11/2014 12:56      unknown       
12/11/2014 13:01      unknown       
12/11/2014 13:06      unknown       
12/11/2014 13:11      unknown       
12/11/2014 13:16      unknown       
12/11/2014 13:21      unknown       
12/11/2014 13:26      unknown       
12/11/2014 13:31      unknown       


Thanks!
0
Comment
Question by:edrz01
[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
  • 5
  • 4
  • 4
13 Comments
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40493842
what is your primary key in this table? and where is devicename?
0
 

Author Comment

by:edrz01
ID: 40493888
The sample data came from a web page. Actual data would come from a view. I just looked at the view and can't identify a primary key. The fields in the ResponseTime view are

NodeID
DateTime
AvgResponseTime
MinResponseTime
MaxResponseTime
PercentLoss
Availability
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40493920
assuming the following columns:
devicename for device name field
tim for date/time field (timestamp)
rtime for response time field (number) (IT SHOULD BE 0 OR -1 OR NULL IF UNKNOWN)

and responsetimetable for the tablename
try this:

SELECT devicename,date(tim), min(rtime), max(rtime)
FROM RESPONSETIMETABLE
WHERE RTIME IS NOT NULL
group by date(tim),devicename

Open in new window


RTIME SHOULD BE EITHER 0 OR -1 OR NULL IF UNKNOWN
YOU SHOULD UPDATE THE SQL ACCORDINGLY
IF 0 THEN YOUR WHERE CLAUSE WOULD BE: WHERE RTIME<>0
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 11

Expert Comment

by:Dany Balian
ID: 40493937
Assuming NodeID is your device name 'ABCDEF'

SELECT NodeID,date(datetime), min(AvgResponseTime), max(AvgResponseTime)
FROM ResponseTime
WHERE AvgResponseTime IS NOT NULL
group by date(datetime),nodeid

Open in new window


if the devicename is not in the table, just remove it from the select and group by:

SELECT date(datetime), min(AvgResponseTime), max(AvgResponseTime)
FROM ResponseTime
WHERE AvgResponseTime IS NOT NULL
group by date(datetime)

Open in new window

0
 

Author Comment

by:edrz01
ID: 40493999
Oh so close. I appreciate the solutions so far.

I could not use the date(datetime) - error 'date' is not a recognized built in function name

I then tried
SELECT cast(datetime as date), min(AvgResponseTime), max(AvgResponseTime)
FROM ResponseTime
WHERE AvgResponseTime IS NOT NULL
group by cast(datetime as date)

and it resulted in data but did not show the time, only the date.

Is it possible to get the date AND time?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40494138
Post some actual sample data in a spreadsheet or a format that is directly loadable into a table.  This is code that needs to be run, and it almost always takes longer to prep sample data than to write the query!
0
 
LVL 11

Expert Comment

by:Dany Balian
ID: 40494173
ok, to get the time, you must do 2 joins which is heavy!
something like this:

SELECT cast(r.datetime as date), min(r.AvgResponseTime) as mintime, min(r2.datetime) as mindatetime, max(r.AvgResponseTime), max(r3.datetime) as maxdatetime
FROM ResponseTime r
inner join responsetime r2 on cast(r2.datetime as date)=cast(r.datetime as date)
inner join responsetime r3 on cast(r3.datetime as date)=cast(r.datetime as date)
WHERE r.datetime IS NOT NULL
group by cast(r.datetime as date), r2.AvgResponseTime, r3.AvgResponseTime
having r2.AvgResponseTime=min(r.AvgResponseTime) and r3.AvgResponseTime=max(r.AvgResponseTime)

Open in new window


a faster solution would be to use subqueries:
but you would have to add a new field on your view called dateofrecord (that has only the date)
to add this field to the view (if you have access to update the main responsetime view)
just add this select at the end of the select part in the view

alter view responsetime 
as select field1, field2, field3, ..., field9

, cast(r.datetime as date) as dateofrecord    // add this before the from part of the view

from ....

Open in new window


SELECT r.dateofrecord, min(r.AvgResponseTime) as mintime, max(r.AvgResponseTime) as maxtime
, (select max(ss.datetime) from responsetime ss where ss.dateofrecord=r.dateofrecord and ss.AvgResponseTime=mintime) 
, (select max(ss.datetime) from responsetime ss where ss.dateofrecord=r.dateofrecord and ss.AvgResponseTime=maxtime) 
FROM ResponseTime r
WHERE r.AvgResponseTimeIS NOT NULL
group by r.dateofrecord

Open in new window

0
 

Author Comment

by:edrz01
ID: 40494183
Here is a sample data dump
ResponseTimeSampleData.xlsx
0
 

Author Comment

by:edrz01
ID: 40494237
Dany Balian - not kidding about being heavy! I took your sample and kicked it off - still running after 10 minutes. I will look at the other solution
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 40494717
I'd use a temp table here to help with efficiency.  I loaded the sample spreadsheet data into a table named "responses" to do this code. I also created a (nonclustered) index on responses ( NodeId, DateTime ) include ( AvgResponseTime ) to speed up the temp table load process (the process of identifying the first non-null value in each group of non-null values).



IF OBJECT_ID('tempdb.dbo.#responses_start') IS NOT NULL
    DROP TABLE #responses_start
CREATE TABLE #responses_start (
    ident int IDENTITY(1, 1) NOT NULL,
    NodeId int NOT NULL,
    DateTime datetime NULL,
    DateTime_Next_Row datetime NULL,
    UNIQUE CLUSTERED ( NodeId, DateTime )
    )

INSERT INTO #responses_start ( NodeId, DateTime )
SELECT r1.NodeID, r1.DateTime
FROM dbo.responses r1
WHERE
    r1.AvgResponseTime IS NOT NULL
    AND (
        SELECT TOP (1) r2.AvgResponseTime
        FROM dbo.responses r2
        WHERE
            r2.NodeID = r1.NodeID AND
            r2.DateTime < r1.DateTime
        ORDER BY r2.DateTime DESC
    ) IS NULL
ORDER BY r1.NodeID, r1.DateTime

UPDATE rs1
SET DateTime_Next_Row = ISNULL(rs2.DateTime, GETDATE())
FROM #responses_start rs1
LEFT OUTER JOIN #responses_start rs2 ON
    rs2.ident = rs1.ident + 1 AND
    rs2.NodeId = rs1.NodeId


SELECT
    MAX(NodeID) AS NodeID,
    MIN(DateTime) AS [Min Datetime],
    MIN(AvgResponseTime) AS [Min Response Time],
    MAX(DateTime) AS [Max Datetime],
    MAX(AvgResponseTime) AS [Max Response Time]
FROM (
    SELECT
        rs.ident, r.NodeID, r.DateTime, r.AvgResponseTime,
        ROW_NUMBER() OVER(PARTITION BY ident ORDER BY AvgResponseTime ASC) AS row_num_min,
        ROW_NUMBER() OVER(PARTITION BY ident ORDER BY AvgResponseTime DESC) AS row_num_max
    FROM dbo.responses r
    LEFT OUTER JOIN #responses_start rs ON
        rs.NodeId = r.NodeId AND
        r.DateTime >= rs.DateTime AND
        r.DateTime < rs.DateTime_Next_Row
    WHERE
        r.AvgResponseTime IS NOT NULL
) AS derived
WHERE
    row_num_min = 1 OR
    row_num_max = 1
GROUP BY ident
0
 

Author Comment

by:edrz01
ID: 40494967
ScottPletcher - I took you sample code and ran it on our SQL. I adjusted the table name and kicked it off - I stopped it after 10 minutes of running.

I then put in some where's hoping that would make it faster - I stopped it again after 10 Minutes.

Looks like I might have to figure out another way or simply tell them "ain't happening"
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40495034
Did you first create the index I mentioned?
Did you leave the temp table fully indexed as written?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40505404
Did you add the index(es)?
What was the final run time?
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

765 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