5

I have a table full of network interface statistics.

InterfaceID TimeStamp                      RCV_bps       XMT_bps
----------- ------------------------------ ------------- -------------
275         Nov 19 2015 12:12AM            1785.593      8.859444
275         Nov 19 2015 12:21AM            1769.675      9.540664
275         Nov 19 2015 12:30AM            1826.036      10.22184
275         Nov 19 2015 12:39AM            1754.356      8.177711
275         Nov 19 2015 12:48AM            1820.504      7.496235
275         Nov 19 2015 12:57AM            1824.645      8.177948
275         Nov 19 2015  1:06AM            1795.941      8.177711
275         Nov 19 2015  1:15AM            1780.149      7.496235
275         Nov 19 2015  1:24AM            1817.059      8.177475
275         Nov 19 2015  1:33AM            1799.519      7.496452
275         Nov 19 2015  1:42AM            1840.726      8.177711
275         Nov 19 2015  1:51AM            3986.634      7.496235
275         Nov 19 2015  2:00AM            1886.375      8.177475
275         Nov 19 2015  2:09AM            1771.905      7.496019
275         Nov 19 2015  2:18AM            1803.834      8.769002
275         Nov 19 2015  2:27AM            1807.718      7.574132
275         Nov 19 2015  2:36AM            1788.274      8.859444
275         Nov 19 2015  2:45AM            1855.727      7.496452
275         Nov 19 2015  2:54AM            1826.8        7.496235
275         Nov 19 2015  3:03AM            1870.37       8.177711

I need to join this to another table that has interface details

InterfaceID InterfaceName DeviceName
----------- ------------- ----------
275         Ethernet-1    Router-A

The requirement is to generate a report that shows the maximum values for RCV_bps and XMT_bps, and show what hour they occurred in.

I know I can get the hour blocks by grouping with a datepart function.

SELECT
     d.DeviceName
    ,d.InterfaceName
    ,r.maxRCV_hour
    ,r.maxRCV
    ,t.maxXMT_hour
    ,t.maxXMT
FROM Details d
INNER JOIN (
    SELECT
        InterfaceID
        ,datepart(hh, s.timestamp) maxRCV_hour
        ,MAX(RCV_bps) maxRCV
    FROM Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
) r ON r.InterfaceID = d.InterfaceID
INNER JOIN (
    SELECT
        InterfaceID
        ,datepart(hh, s.timestamp) maxXMT_hour
        ,MAX(XMT_bps) maxXMT
    FROM Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
) t ON t.InterfaceID = d.InterfaceID

But I cannot see how to take those values and return only the max for rcv and xmt. Based on the above time limits for only the last 1 day, I am getting 24 rows of data for hours 0 - 23 in each subquery...

Subquery 'r':

InterfaceID maxRCV_hour maxRCV
----------- ----------- -------------
275         0           1826.036
275         1           3986.634
275         2           1886.375
275         3           1895.214
275         4           1874.77
275         5           3999.568
275         6           2322.544
275         7           3327.41
275         8           6931.27
275         9           9165.674
275         10          9548.88
275         11          7349.096
275         12          5908.13
275         13          7247.869
275         14          9452.746
275         15          7118.653
275         16          5124.106
275         17          4012.313
275         18          3444.12
275         19          1236.745
275         20          2314.144
275         21          3430.802
275         22          1307.774
275         23          1594.491

Subquery 't':

InterfaceID maxXMT_hour maxXMT
----------- ----------- -------------
275         0           10.22184
275         1           8.177711
275         2           8.859444
275         3           12.26692
275         4           12.26657
275         5           12.26657
275         6           12.26657
275         7           8.859187
275         8           2969.632
275         9           13.60028
275         10          9.68881
275         11          8.177711
275         12          8.177711
275         13          7.496452
275         14          7.496452
275         15          8.177948
275         16          8.17783
275         17          8.859154
275         18          8.859444
275         19          8.859187
275         20          8.199979
275         21          8.859444
275         22          8.177761
275         23          8.859699

But when I run the full query I get 576 rows (so something about the way I am joining things is definitely off)

What I want is 1 row of data showing the largest values and their respective hours for both the RCV and XMT side.

Based on my results in the subqueries above, I would like the end result to look like this:

DeviceName InterfaceName maxRCV_hour maxRCV  maxXMT_hour maxXMT
---------- ------------- ----------- ------- ----------- --------
Router-A   Ethernet-1       10       9548.88     8       2969.632

My assumption is that I need to use a CTE or windowing, but those are a little beyond my current strengths and I could use some assistance figuring this out.

Any help is greatly appreciated!!!

  • Do you need to return just one row with global maximum, or one row for each date? – Vladimir Baranov Nov 20 '15 at 10:58
  • It would be 1 row for each day that reflects the highest values that day, and the hour they occured in. spaghettidba had a solid solution that works for me, but I am always open to new things if you have any suggestions. :) – zackm Nov 20 '15 at 23:35
2

You don't necessarily need to use CTEs or windowing functions: there are many ways to skin this cat.

This is the simplest that comes to my mind:

-- SAMPLE DATA:
-- remember to post data this way next time you ask for help,
-- so that people don't have to do it for you
IF OBJECT_ID('tempdb..#statistics') IS NOT NULL
    DROP TABLE #statistics;
GO
CREATE TABLE #statistics(
   InterfaceID int NOT NULL 
  ,TimeStamp   DATETIME  NOT NULL
  ,RCV_bps     decimal(9,3) NOT NULL
  ,XMT_bps     decimal(9,6) NOT NULL
);
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:12AM','1785.593','8.859444');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:21AM','1769.675','9.540664');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:30AM','1826.036','10.22184');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:39AM','1754.356','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:48AM','1820.504','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015 12:57AM','1824.645','8.177948');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:06AM','1795.941','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:15AM','1780.149','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:24AM','1817.059','8.177475');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:33AM','1799.519','7.496452');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:42AM','1840.726','8.177711');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  1:51AM','3986.634','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:00AM','1886.375','8.177475');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:09AM','1771.905','7.496019');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:18AM','1803.834','8.769002');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:27AM','1807.718','7.574132');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:36AM','1788.274','8.859444');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:45AM','1855.727','7.496452');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  2:54AM','1826.8','7.496235');
INSERT INTO #statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES ('275','Nov 20 2015  3:03AM','1870.37','8.177711');
GO

IF OBJECT_ID('tempdb..#details') IS NOT NULL
    DROP TABLE #details;
GO
CREATE TABLE #details (
    InterfaceID int NOT NULL,
    InterfaceName varchar(50) NOT NULL,
    DeviceName varchar(50) NOT NULL
);

INSERT INTO #details VALUES(275, 'Ethernet-1', 'Router-A');
GO

.

-- POSSIBLE SOLUTION:
-- use CROSS APPLY TOP(1)/ORDER BY to find the value
SELECT
     d.DeviceName
    ,d.InterfaceName
    ,r.maxRCV_hour
    ,r.maxRCV
    ,t.maxXMT_hour
    ,t.maxXMT
FROM #Details d
CROSS APPLY (
    SELECT TOP(1)
        InterfaceID
        ,datepart(hh, s.timestamp) maxRCV_hour
        ,MAX(RCV_bps) maxRCV
    FROM #Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
    HAVING s.InterfaceID = d.InterfaceID
    ORDER BY maxRCV DESC
) AS r
CROSS APPLY (
    SELECT TOP(1)
        InterfaceID
        ,datepart(hh, s.timestamp) maxXMT_hour
        ,MAX(XMT_bps) maxXMT
    FROM #Statistics s
    WHERE TimeStamp > (GetDate()-1)
    GROUP BY
        InterfaceID
        ,datepart(hh, s.timestamp)
    HAVING s.InterfaceID = d.InterfaceID
    ORDER BY maxXMT DESC
) AS t;
  • works like a dream! thank you for the assistance. (and I apologize for not building out my sample data, I'll remember from now on!) – zackm Nov 20 '15 at 23:33
3

I think that answer by @spaghettidba is too complicated. There is no need for GROUP BY. It is a simple top-n-per-group, with grouping by InterfaceID. Especially if you filter statistics to one day. In this answer I used CROSS APPLY, as @spaghettidba. See the link above for other variants, their comparison and what indexes are useful in this case.

I've taken the sample data from the answer by @spaghettidba.

DECLARE @statistics TABLE (
   InterfaceID int NOT NULL 
  ,TimeStamp   DATETIME  NOT NULL
  ,RCV_bps     decimal(9,3) NOT NULL
  ,XMT_bps     decimal(9,6) NOT NULL
);

INSERT INTO @statistics(InterfaceID,TimeStamp,RCV_bps,XMT_bps) VALUES 
('275','Nov 20 2015 12:12AM','1785.593','8.859444'),
('275','Nov 20 2015 12:21AM','1769.675','9.540664'),
('275','Nov 20 2015 12:30AM','1826.036','10.22184'),
('275','Nov 20 2015 12:39AM','1754.356','8.177711'),
('275','Nov 20 2015 12:48AM','1820.504','7.496235'),
('275','Nov 20 2015 12:57AM','1824.645','8.177948'),
('275','Nov 20 2015  1:06AM','1795.941','8.177711'),
('275','Nov 20 2015  1:15AM','1780.149','7.496235'),
('275','Nov 20 2015  1:24AM','1817.059','8.177475'),
('275','Nov 20 2015  1:33AM','1799.519','7.496452'),
('275','Nov 20 2015  1:42AM','1840.726','8.177711'),
('275','Nov 20 2015  1:51AM','3986.634','7.496235'),
('275','Nov 20 2015  2:00AM','1886.375','8.177475'),
('275','Nov 20 2015  2:09AM','1771.905','7.496019'),
('275','Nov 20 2015  2:18AM','1803.834','8.769002'),
('275','Nov 20 2015  2:27AM','1807.718','7.574132'),
('275','Nov 20 2015  2:36AM','1788.274','8.859444'),
('275','Nov 20 2015  2:45AM','1855.727','7.496452'),
('275','Nov 20 2015  2:54AM','1826.800','7.496235'),
('275','Nov 20 2015  3:03AM','1870.370','8.177711');

DECLARE @details TABLE  (
    InterfaceID int NOT NULL,
    InterfaceName varchar(50) NOT NULL,
    DeviceName varchar(50) NOT NULL
);

INSERT INTO @details VALUES(275, 'Ethernet-1', 'Router-A');

This query explicitly limits statistics to one day. Each CROSS APPLY finds a maximum value and a corresponding timestamp. Then I truncate the found timestamp to an hour boundary using DATEADD/DATEDIFF. If you need just an hour, wrap it into DATEPART(hour, DATEADD......).

SELECT
    D.InterfaceID
    ,D.InterfaceName
    ,D.DeviceName
    ,CA_RVC.tsRCV
    -- Truncate timestamp to 1 hour.
    ,DATEADD(hour, DATEDIFF(hour, '20010101', CA_RVC.tsRCV), '20010101') AS HourRCV
    ,CA_RVC.RCV_bps
    ,CA_XMT.tsXMT
    -- Truncate timestamp to 1 hour.
    ,DATEADD(hour, DATEDIFF(hour, '20010101', CA_XMT.tsXMT), '20010101') AS HourXMT
    ,CA_XMT.XMT_bps
FROM
    @details AS D
    CROSS APPLY
    (
        SELECT TOP(1)
            S.RCV_bps
            ,S.TimeStamp AS tsRCV
        FROM @statistics AS S
        WHERE
            S.InterfaceID = D.InterfaceID
            AND S.TimeStamp >= '2015-11-20'
            AND S.TimeStamp < '2015-11-21'
        ORDER BY S.RCV_bps DESC
    ) AS CA_RVC
    CROSS APPLY
    (
        SELECT TOP(1)
            S.XMT_bps
            ,S.TimeStamp AS tsXMT
        FROM @statistics AS S
        WHERE
            S.InterfaceID = D.InterfaceID
            AND S.TimeStamp >= '2015-11-20'
            AND S.TimeStamp < '2015-11-21'
        ORDER BY S.XMT_bps DESC
    ) AS CA_XMT
;

It is easy to extend the query further to return not just one row per InterfaceID, but one row per InterfaceID and each day that appears in statistics. If you make up a different set of sample data, which contains several days, and show the expected result of the query, I can do it for you.

  • You're absolutely right: my solution was the simplest in terms of modifications to the original query, not the simplest in terms of complesity. I was just being lazy :-) – spaghettidba Nov 21 '15 at 22:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.