Solved

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

Posted on 2014-12-11

Microsoft SQL Server

1 solution

101 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!

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

Join The Community

13 Comments

LVL 11

Expert Comment

by:Dany Balian

ID: 404938422014-12-11

what is your primary key in this table? and where is devicename?

Author Comment

by:edrz01

ID: 404938882014-12-11

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

LVL 11

Expert Comment

by:Dany Balian

ID: 404939202014-12-11

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

LVL 11

Expert Comment

by:Dany Balian

ID: 404939372014-12-11

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

Author Comment

by:edrz01

ID: 404939992014-12-11

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?

LVL 69

Expert Comment

by:Scott Pletcher

ID: 404941382014-12-11

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!

LVL 11

Expert Comment

by:Dany Balian

ID: 404941732014-12-11

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

Author Comment

by:edrz01

ID: 404941832014-12-11

Here is a sample data dump
ResponseTimeSampleData.xlsx

Author Comment

by:edrz01

ID: 404942372014-12-11

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

LVL 69

Accepted Solution

by:Scott Pletcher

Scott Pletcher earned 500 total points

ID: 404947172014-12-11

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

Author Comment

by:edrz01

ID: 404949672014-12-11

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"

LVL 69

Expert Comment

by:Scott Pletcher

ID: 404950342014-12-11

Did you first create the index I mentioned?
Did you leave the temp table fully indexed as written?

LVL 69

Expert Comment

by:Scott Pletcher

ID: 405054042014-12-17

Did you add the index(es)?
What was the final run time?

Featured Post

Question has a verified solution.

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

5 Lessons the Delta Outage Should Teach Us About Datacenter Security and Disaster Prevention

Article by: Concerto Cloud

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…