SQL Server 2008R2 - MAX Result and Hour per Day

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.ma

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-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.

StackExchange Database Administrators Q#121650, answer score: 3