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
71 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
  • 5
  • 4
  • 4
13 Comments
 
LVL 11

Expert Comment

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

Author Comment

by:edrz01
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 11

Expert Comment

by:Dany Balian
Comment Utility
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
Comment Utility
Here is a sample data dump
ResponseTimeSampleData.xlsx
0
 

Author Comment

by:edrz01
Comment Utility
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:
ScottPletcher earned 500 total points
Comment Utility
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
Comment Utility
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:ScottPletcher
Comment Utility
Did you first create the index I mentioned?
Did you leave the temp table fully indexed as written?
0
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
Did you add the index(es)?
What was the final run time?
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help on Setting up an identical test database 17 50
Help with SQL Query 23 39
Backup Job question 4 17
Test a query 23 11
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now