locked
How to sum a query for every 5 minutes RRS feed

  • Question

  • Hi,

    I have a query that sum data every round minute:

    select NetModem.SourceHub,
    network.NetworkName,
    QOSGroupPrimary.Name AS QOS,
    Hourly_nms_qos_stats.direction,
    Hourly_nms_qos_stats.DateHour,
    round(sum(Hourly_nms_qos_stats.bw_requested/50)/count(*),2) as bw_requested,
    round(sum(Hourly_nms_qos_stats.bw_allocated/50)/count(*),2) as bw_allocated,
    round(sum(Hourly_nms_qos_stats.free_bandwidth/50)/count(*),2) as free_bandwidth
    from    
    netmodem inner join Network ON network.SourceHub = netmodem.sourcehub and network.NetworkId = NetModem.NetworkId  inner join
    VirtualRemote on NetModem.NetModemId=VirtualRemote.RemoteId and NetModem.SourceHub=VirtualRemote.SourceHub inner join
    Hourly_nms_qos_stats on Hourly_nms_qos_stats.unique_id=VirtualRemote.ID and Hourly_nms_qos_stats.SourceHub=VirtualRemote.SourceHub inner join
    QOSGroupServiceProfile ON NetModem.DownstreamQOSProfileId = QOSGroupServiceProfile.ID AND 
    NetModem.SourceHub = QOSGroupServiceProfile.SourceHub INNER JOIN
    QOSGroup ON QOSGroupServiceProfile.QOSGroupId = QOSGroup.ID AND QOSGroup.SourceHub = QOSGroupServiceProfile.SourceHub INNER JOIN
    QOSGroup AS QOSGroupPrimary ON QOSGroup.ParentId = QOSGroupPrimary.ID AND QOSGroup.SourceHub = QOSGroupPrimary.SourceHub 
    WHERE     (NetModem.NetModemTypeId = 3) AND (NetModem.ActiveStatus = 1)
    group by NetModem.SourceHub,
    network.NetworkName,
    QOSGroupPrimary.Name,
    Hourly_nms_qos_stats.DateHour,
    Hourly_nms_qos_stats.direction

    This is an example of the data it gets:

    SourceHub NetworkName QOS direction direction DateHour bw_requested bw_allocated free_bandwidth
    NMS-CRAWFORD-NSS10-T11 T11N_Evolution_Network_ Advantage 0 2012-10-27 00:01:00 1.92 1.92 15897.6
    NMS-CRAWFORD-NSS10-T11 T11N_Evolution_Network_ Advantage 0 2012-10-27 00:02:00 30742501.66 206229.98 13366.53
    NMS-CRAWFORD-NSS10-T11 T11N_Evolution_Network_ Advantage 1 2012-10-27 00:02:00 264692.27 257198.67 0
    NMS-CRAWFORD-NSS10-T11 T11N_Evolution_Network_ Advantage 0 2012-10-27 00:03:00 37810565.32 120552.92 15882.08

    My question is: How do I sum the data every 5 minutes?

    Sunday, October 28, 2012 2:42 PM

Answers

  • Apparently the data type for Hourly_nms_qos_stats.DateHour is smalldatetime.

    Replace both occurrences of this column with

    dateadd(minute, -datepart(minute, Hourly_nms_qos_stats.DateHour) % 5,
                    Hourly_nms_qos_stats.DateHour)

    This will count 00:00 to 00:04 to 00:00, 00:05 to 00:09 to 00:05 etc.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, October 28, 2012 3:02 PM
    • Marked as answer by Naomi N Sunday, October 28, 2012 6:26 PM
    Sunday, October 28, 2012 2:57 PM

All replies

  • Apparently the data type for Hourly_nms_qos_stats.DateHour is smalldatetime.

    Replace both occurrences of this column with

    dateadd(minute, -datepart(minute, Hourly_nms_qos_stats.DateHour) % 5,
                    Hourly_nms_qos_stats.DateHour)

    This will count 00:00 to 00:04 to 00:00, 00:05 to 00:09 to 00:05 etc.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Proposed as answer by Naomi N Sunday, October 28, 2012 3:02 PM
    • Marked as answer by Naomi N Sunday, October 28, 2012 6:26 PM
    Sunday, October 28, 2012 2:57 PM
  • Thanks! It works perfectly!
    • Marked as answer by ThomasYaris30 Sunday, October 28, 2012 5:18 PM
    • Unmarked as answer by Naomi N Sunday, October 28, 2012 6:26 PM
    Sunday, October 28, 2012 5:18 PM