[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
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
?
122 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 70

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 70

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 70

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 70

Expert Comment

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

Featured Post

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

656 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