SQL Group by time interval

Hi need assistance with the sql code in grouping this output
The contains 144 10 minute intervals
I'd like to break it down to hourly intervals and 2, 3 ,4 etc.. hour intervals
So if i grouped it by hour i would have roughly 72 rows

Any help appreciated in pointing me in the right direction

DateStamp,CPUUsage,CPUReady,MemoryUsage,DiskIOPerfRead,DiskIOPerfWrite,NicPerf
2015-10-20 00:00:00,47,9.79,79,0.68,5.54,0.006
2015-10-20 00:10:00,43,9.56,79,0.59,5.16,0.005
2015-10-20 00:20:00,43,9.60,79,0.53,6.36,0.006
2015-10-20 00:30:00,44,9.65,79,0.43,8.76,0.007
2015-10-20 00:40:00,49,10.06,80,2.62,4.75,0.007
2015-10-20 00:50:00,45,9.68,79,0.28,4.75,0.015
2015-10-20 01:00:00,46,9.68,79,0.23,3.14,0.006
2015-10-20 01:10:00,47,9.75,78,0.41,3.25,0.006
2015-10-20 01:20:00,48,9.83,78,0.33,4.36,0.006
2015-10-20 01:30:00,48,9.77,79,0.43,4.54,0.005
2015-10-20 01:40:00,52,10.24,79,2.66,6.22,0.007
2015-10-20 01:50:00,47,9.82,79,0.17,4.15,0.006
2015-10-20 02:00:00,48,9.91,79,0.64,12.54,0.008
2015-10-20 02:10:00,47,9.82,79,0.58,6.38,0.007
2015-10-20 02:20:00,47,9.80,79,0.70,7.77,0.008
2015-10-20 02:30:00,46,9.78,80,0.48,9.94,0.008
2015-10-20 02:40:00,50,10.13,80,3.22,4.54,0.006
2015-10-20 02:50:00,45,9.74,80,0.32,4.57,0.006
2015-10-20 03:00:00,45,9.67,79,0.44,6.85,0.006
2015-10-20 03:10:00,44,9.61,79,0.38,4.06,0.006
2015-10-20 03:20:00,44,9.63,79,0.43,3.20,0.006
2015-10-20 03:30:00,43,9.55,79,0.23,4.81,0.006
2015-10-20 03:40:00,48,10.02,79,4.14,6.70,0.007
2015-10-20 03:50:00,44,9.66,79,0.26,4.76,0.007
2015-10-20 04:00:00,44,10.82,79,0.34,3.67,0.007
2015-10-20 04:10:00,43,9.58,79,0.41,3.36,0.007
2015-10-20 04:20:00,43,9.57,79,0.41,3.39,0.006
2015-10-20 04:30:00,43,9.59,79,5.60,3.72,0.006
2015-10-20 04:40:00,46,9.83,79,2.42,3.79,0.006
2015-10-20 04:51:00,43,9.65,79,0.18,3.66,0.005
2015-10-20 05:01:00,42,9.51,79,0.23,3.24,0.005
2015-10-20 05:11:00,42,9.49,79,0.33,3.46,0.005
2015-10-20 05:21:00,42,9.49,79,0.35,3.22,0.006
2015-10-20 05:31:00,41,9.50,79,1.67,3.15,0.005
2015-10-20 05:41:00,46,9.87,79,2.41,3.64,0.006
2015-10-20 05:51:00,43,9.62,79,0.12,3.46,0.005
2015-10-20 06:01:00,42,9.49,79,0.31,3.42,0.005
2015-10-20 06:11:00,42,9.53,79,0.34,3.38,0.005
2015-10-20 06:21:00,46,9.75,79,1.75,3.64,0.005
2015-10-20 06:31:00,43,9.57,79,0.21,3.17,0.005
2015-10-20 06:41:00,44,9.71,79,2.27,3.52,0.006
2015-10-20 06:51:00,41,9.53,79,3.19,3.17,0.004
2015-10-20 07:01:00,40,9.44,79,0.26,3.08,0.003
2015-10-20 07:11:00,41,9.48,79,1.79,3.20,0.003
2015-10-20 07:21:00,42,9.50,79,1.72,3.82,0.003
2015-10-20 07:31:00,41,9.44,79,0.11,3.46,0.003
2015-10-20 07:41:00,46,9.88,79,4.00,3.63,0.005
2015-10-20 07:51:00,44,9.68,79,3.11,3.31,0.003
2015-10-20 08:01:00,44,9.60,79,0.19,3.13,0.004
2015-10-20 08:11:00,43,9.54,79,1.87,3.10,0.003
2015-10-20 08:21:00,46,9.75,79,1.32,3.70,0.004
2015-10-20 08:31:00,44,9.59,79,3.22,3.47,0.004
2015-10-20 08:41:00,48,9.99,79,5.85,3.60,0.005
2015-10-20 08:51:00,47,9.81,79,3.14,3.50,0.004
2015-10-20 09:01:00,47,9.80,80,1.76,3.64,0.004
2015-10-20 09:11:00,48,9.82,80,0.43,3.21,0.004
2015-10-20 09:21:00,49,9.90,80,1.77,3.42,0.004
2015-10-20 09:31:00,49,9.87,80,0.17,3.17,0.003
2015-10-20 09:41:00,51,10.21,80,3.96,3.72,0.004
2015-10-20 09:51:00,49,9.95,80,3.32,3.35,0.004
2015-10-20 10:01:00,46,9.72,80,0.27,3.17,0.003
2015-10-20 10:11:00,48,9.83,80,4.83,3.64,0.003
2015-10-20 10:21:00,46,9.71,80,1.81,3.08,0.003
2015-10-20 10:31:00,46,9.75,80,1.64,4.25,0.004
2015-10-20 10:41:00,55,10.52,80,6.89,3.89,0.004
2015-10-20 10:51:00,45,9.74,80,4.76,3.55,0.004
2015-10-20 11:01:00,44,9.63,80,4.75,3.13,0.003
2015-10-20 11:11:00,41,9.50,80,1.81,3.14,0.004
2015-10-20 11:21:00,42,9.58,80,1.76,3.42,0.003
2015-10-20 11:31:00,42,9.51,80,1.74,3.34,0.003
2015-10-20 11:41:00,46,9.88,80,2.39,3.54,0.004
2015-10-20 11:51:00,42,9.60,80,0.10,3.22,0.004
2015-10-20 12:01:00,42,9.53,81,7.09,3.42,0.003
2015-10-20 12:11:00,41,9.50,81,1.56,3.24,0.005
2015-10-20 12:21:00,41,9.50,81,0.26,3.37,0.005
2015-10-20 12:31:00,43,9.58,80,2.56,3.18,0.005
2015-10-20 12:41:00,47,9.96,81,5.42,3.84,0.006
2015-10-20 12:51:00,44,9.69,81,0.10,3.54,0.005
2015-10-20 13:01:00,43,9.55,81,0.32,3.16,0.005
2015-10-20 13:11:00,44,9.62,81,0.30,3.23,0.005
2015-10-20 13:21:00,44,9.65,81,0.24,3.22,0.005
2015-10-20 13:31:00,42,9.53,81,3.35,3.18,0.009
2015-10-20 13:41:00,46,9.86,81,6.96,4.02,0.007
2015-10-20 13:51:00,43,9.62,81,0.51,3.61,0.008
2015-10-20 14:01:00,42,9.54,81,0.28,3.57,0.011
2015-10-20 14:11:00,42,9.56,81,0.76,3.53,0.008
2015-10-20 14:21:00,53,10.19,81,1.41,3.61,0.006
2015-10-20 14:31:00,42,9.53,81,0.20,3.25,0.008
2015-10-20 14:41:00,46,9.92,81,2.50,4.19,0.009
2015-10-20 14:51:00,43,9.63,81,0.25,3.40,0.008
2015-10-20 15:01:00,40,9.46,81,0.25,3.15,0.006
2015-10-20 15:11:00,41,9.47,81,0.40,3.14,0.006
2015-10-20 15:21:00,52,10.22,81,0.24,3.67,0.006
2015-10-20 15:31:00,49,9.93,81,0.13,3.47,0.006
2015-10-20 15:41:00,57,10.78,81,2.51,4.13,0.010
2015-10-20 15:51:00,47,9.83,81,0.15,3.64,0.012
2015-10-20 16:01:00,54,10.25,81,0.24,3.61,0.013
2015-10-20 16:11:00,45,9.67,81,0.35,3.42,0.008
2015-10-20 16:21:00,41,9.49,81,0.40,3.22,0.006
2015-10-20 16:31:00,40,9.44,81,0.19,3.27,0.007
2015-10-20 16:41:00,45,9.84,81,2.32,4.73,0.093
2015-10-20 16:51:00,54,10.30,81,0.32,3.83,0.009
2015-10-20 17:01:00,48,9.91,81,0.27,4.38,0.010
2015-10-20 17:11:00,53,10.22,81,0.48,3.51,0.007
2015-10-20 17:21:00,53,10.25,81,0.34,3.30,0.006
2015-10-20 17:31:00,47,9.78,81,0.15,2.99,0.005
2015-10-20 17:41:00,48,10.07,81,2.39,4.76,0.006
2015-10-20 17:51:00,54,10.35,81,0.37,4.72,0.006
2015-10-20 18:01:00,43,9.58,81,0.25,3.35,0.006
2015-10-20 18:11:00,41,9.48,81,0.34,3.38,0.006
2015-10-20 18:21:00,41,9.44,81,0.33,3.14,0.005
2015-10-20 18:31:00,41,9.47,81,0.24,3.12,0.006
2015-10-20 18:41:00,47,9.96,81,2.43,3.68,0.006
2015-10-20 18:51:00,43,9.61,81,2.87,3.23,0.006
2015-10-20 19:01:00,42,9.55,81,1.83,3.15,0.005
2015-10-20 19:11:00,41,9.49,81,1.88,3.39,0.005
2015-10-20 19:21:00,41,9.50,81,0.31,3.13,0.006
2015-10-20 19:31:00,40,9.44,81,0.58,3.39,0.005
2015-10-20 19:41:00,47,9.97,81,7.03,4.01,0.009
2015-10-20 19:51:00,46,9.83,81,0.33,3.38,0.005
2015-10-20 20:01:00,43,9.57,81,0.27,3.51,0.005
2015-10-20 20:11:00,45,9.68,81,0.42,3.43,0.005
2015-10-20 20:21:00,46,9.68,81,0.23,3.20,0.006
2015-10-20 20:31:00,44,9.60,81,0.13,3.11,0.005
2015-10-20 20:41:00,48,10.02,81,6.97,3.75,0.006
2015-10-20 20:51:00,47,9.85,81,0.64,8.53,0.007
2015-10-20 21:01:00,43,9.53,81,0.21,3.14,0.005
2015-10-20 21:11:00,44,9.62,81,0.38,3.13,0.005
2015-10-20 21:21:00,40,9.40,82,0.17,3.39,0.005
2015-10-20 21:31:00,40,9.38,81,0.16,3.11,0.005
2015-10-20 21:41:00,46,9.80,81,2.40,3.67,0.005
2015-10-20 21:51:00,43,9.57,81,1.66,3.35,0.007
2015-10-20 22:01:00,43,9.52,81,0.29,3.22,0.007
2015-10-20 22:11:00,41,9.45,82,1.97,5.08,0.005
2015-10-20 22:21:00,41,9.50,82,1.79,5.80,0.005
2015-10-20 22:31:00,42,9.52,82,0.21,5.74,0.005
2015-10-20 22:41:00,49,10.02,82,2.55,5.53,0.006
2015-10-20 22:51:00,45,9.72,82,0.11,6.57,0.005
2015-10-20 23:01:00,53,10.29,82,0.23,3.67,0.008
2015-10-20 23:11:00,51,10.04,82,0.39,6.67,0.005
2015-10-20 23:21:00,41,9.49,82,0.24,5.46,0.005
2015-10-20 23:31:00,41,9.46,82,0.15,8.91,0.005
2015-10-20 23:41:00,44,9.72,83,2.32,7.62,0.006
2015-10-20 23:51:00,42,9.53,82,0.10,3.22,0.005

Open in new window

LVL 4
Mauro CazabonnetSenior .NET Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tom ChadaraviciusSenior DeveloperCommented:
I do not have your table name, so I am proposing a sketch
SELECT *, datediff(hour, DateStamp,getutcdate()) AS cpuage FROM [yourtablename]
GROUP BY DateStamp HAVING cpuage > 1 ORDER BY cpuage DESC;

Open in new window

I keep my fingers crossed, because I can not run my query against your table to test.
Tom
Tom ChadaraviciusSenior DeveloperCommented:
Oops, me previous suggestion would show items older than one hour. The below uses "minute" and should show items older than 59 minutes.
SELECT *, datediff(minute, DateStamp,getutcdate()) AS cpuage FROM [yourtablename]
GROUP BY DateStamp HAVING cpuage > 59 ORDER BY cpuage DESC;

Open in new window

Tom ChadaraviciusSenior DeveloperCommented:
I have imported your data,and here's my interim solution I could test.  The HAVING clause still needs to be addressed
SELECT
datediff(minute, DateStamp,getutcdate()) AS cpuage,
A.DateStamp
FROM
tbl_cpu AS A
GROUP BY
A.DateStamp
ORDER BY
cpuage DESC

Open in new window

Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

PortletPaulEE Topic AdvisorCommented:
72 intervals per day = 20 minute intervals.

This query:
SELECT TOP (10)
      Xminutes
    , Xminutes % 20 as ToDeductMinutes
    , DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101') as "20 Min Intervals"
    , [DateStamp]
FROM Table1
CROSS APPLY (
      SELECT DATEDIFF(MINUTE, '20000101', [DateStamp])
      ) CA (Xminutes)
ORDER BY
      [DateStamp]
;

Open in new window

Produces the following result. The minutes from 2002-01-01 and the [DateStamp] are calculated then the remainder of (those minutes divided by 20) is deducted (i.e. rounds down) to closest 20 minute interval. Note the CROSS APPLY is used so we can avoid repeating the function call and reuse the column alias in subsequent calculations.
| Xminutes | ToDeductMinutes |          20 Min Intervals |                 DateStamp |
|----------|-----------------|---------------------------|---------------------------|
|  8310240 |               0 | October, 20 2015 00:00:00 | October, 20 2015 00:00:00 |
|  8310250 |              10 | October, 20 2015 00:00:00 | October, 20 2015 00:10:00 |
|  8310260 |               0 | October, 20 2015 00:20:00 | October, 20 2015 00:20:00 |
|  8310270 |              10 | October, 20 2015 00:20:00 | October, 20 2015 00:30:00 |
|  8310280 |               0 | October, 20 2015 00:40:00 | October, 20 2015 00:40:00 |
|  8310290 |              10 | October, 20 2015 00:40:00 | October, 20 2015 00:50:00 |
|  8310300 |               0 | October, 20 2015 01:00:00 | October, 20 2015 01:00:00 |
|  8310310 |              10 | October, 20 2015 01:00:00 | October, 20 2015 01:10:00 |
|  8310320 |               0 | October, 20 2015 01:20:00 | October, 20 2015 01:20:00 |
|  8310330 |              10 | October, 20 2015 01:20:00 | October, 20 2015 01:30:00 |

Open in new window

The reference date I have used (2000-01-01) is arbitrary, it could be any date you choose but I wanted to avoid any possibility of the number of minutes exceeding the int data type limit.

So, using that same technique we apply it to your data via a GROUP BY.
nb I have simply used SUM() on all other columns.
SELECT
      DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101') AS DateStamp
    , SUM(CPUUsage) AS CPUUsage
    , SUM(CPUReady) AS CPUReady
    , SUM(MemoryUsage) AS MemoryUsage
    , SUM(DiskIOPerfRead) AS DiskIOPerfRead
    , SUM(DiskIOPerfWrite) AS DiskIOPerfWrite
    , SUM(NicPerf) AS NicPerf
FROM table1
CROSS APPLY (
      SELECT DATEDIFF(MINUTE, '20000101', [DateStamp])
      ) CA (Xminutes)
GROUP BY
      DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101')
;

Open in new window

Full details:
**MS SQL Server 2014 Schema Setup**:

    CREATE TABLE Table1
    ([DateStamp] datetime,[CPUUsage] int,[CPUReady] int,[MemoryUsage] int,[DiskIOPerfRead] numeric,[DiskIOPerfWrite] int,[NicPerf] int)
    ;
    
    INSERT INTO Table1
    ([DateStamp],[CPUUsage],[CPUReady],[MemoryUsage],[DiskIOPerfRead],[DiskIOPerfWrite],[NicPerf])
    VALUES
    ('2015-10-20 00:00:00',47,9.79,79,0.68,5.54,0.006),
    ('2015-10-20 00:10:00',43,9.56,79,0.59,5.16,0.005),
    ('2015-10-20 00:20:00',43,9.60,79,0.53,6.36,0.006),
    ('2015-10-20 00:30:00',44,9.65,79,0.43,8.76,0.007),
    ('2015-10-20 00:40:00',49,10.06,80,2.62,4.75,0.007),
    ('2015-10-20 00:50:00',45,9.68,79,0.28,4.75,0.015),
    ('2015-10-20 01:00:00',46,9.68,79,0.23,3.14,0.006),
    ('2015-10-20 01:10:00',47,9.75,78,0.41,3.25,0.006),
    ('2015-10-20 01:20:00',48,9.83,78,0.33,4.36,0.006),
    ('2015-10-20 01:30:00',48,9.77,79,0.43,4.54,0.005),
    ('2015-10-20 01:40:00',52,10.24,79,2.66,6.22,0.007),
    ('2015-10-20 01:50:00',47,9.82,79,0.17,4.15,0.006),
    ('2015-10-20 02:00:00',48,9.91,79,0.64,12.54,0.008),
    ('2015-10-20 02:10:00',47,9.82,79,0.58,6.38,0.007),
    ('2015-10-20 02:20:00',47,9.80,79,0.70,7.77,0.008),
    ('2015-10-20 02:30:00',46,9.78,80,0.48,9.94,0.008),
    ('2015-10-20 02:40:00',50,10.13,80,3.22,4.54,0.006),
    ('2015-10-20 02:50:00',45,9.74,80,0.32,4.57,0.006),
    ('2015-10-20 03:00:00',45,9.67,79,0.44,6.85,0.006),
    ('2015-10-20 03:10:00',44,9.61,79,0.38,4.06,0.006),
    ('2015-10-20 03:20:00',44,9.63,79,0.43,3.20,0.006),
    ('2015-10-20 03:30:00',43,9.55,79,0.23,4.81,0.006),
    ('2015-10-20 03:40:00',48,10.02,79,4.14,6.70,0.007),
    ('2015-10-20 03:50:00',44,9.66,79,0.26,4.76,0.007),
    ('2015-10-20 04:00:00',44,10.82,79,0.34,3.67,0.007),
    ('2015-10-20 04:10:00',43,9.58,79,0.41,3.36,0.007),
    ('2015-10-20 04:20:00',43,9.57,79,0.41,3.39,0.006),
    ('2015-10-20 04:30:00',43,9.59,79,5.60,3.72,0.006),
    ('2015-10-20 04:40:00',46,9.83,79,2.42,3.79,0.006),
    ('2015-10-20 04:51:00',43,9.65,79,0.18,3.66,0.005),
    ('2015-10-20 05:01:00',42,9.51,79,0.23,3.24,0.005),
    ('2015-10-20 05:11:00',42,9.49,79,0.33,3.46,0.005),
    ('2015-10-20 05:21:00',42,9.49,79,0.35,3.22,0.006),
    ('2015-10-20 05:31:00',41,9.50,79,1.67,3.15,0.005),
    ('2015-10-20 05:41:00',46,9.87,79,2.41,3.64,0.006),
    ('2015-10-20 05:51:00',43,9.62,79,0.12,3.46,0.005),
    ('2015-10-20 06:01:00',42,9.49,79,0.31,3.42,0.005),
    ('2015-10-20 06:11:00',42,9.53,79,0.34,3.38,0.005),
    ('2015-10-20 06:21:00',46,9.75,79,1.75,3.64,0.005),
    ('2015-10-20 06:31:00',43,9.57,79,0.21,3.17,0.005),
    ('2015-10-20 06:41:00',44,9.71,79,2.27,3.52,0.006),
    ('2015-10-20 06:51:00',41,9.53,79,3.19,3.17,0.004),
    ('2015-10-20 07:01:00',40,9.44,79,0.26,3.08,0.003),
    ('2015-10-20 07:11:00',41,9.48,79,1.79,3.20,0.003),
    ('2015-10-20 07:21:00',42,9.50,79,1.72,3.82,0.003),
    ('2015-10-20 07:31:00',41,9.44,79,0.11,3.46,0.003),
    ('2015-10-20 07:41:00',46,9.88,79,4.00,3.63,0.005),
    ('2015-10-20 07:51:00',44,9.68,79,3.11,3.31,0.003),
    ('2015-10-20 08:01:00',44,9.60,79,0.19,3.13,0.004),
    ('2015-10-20 08:11:00',43,9.54,79,1.87,3.10,0.003),
    ('2015-10-20 08:21:00',46,9.75,79,1.32,3.70,0.004),
    ('2015-10-20 08:31:00',44,9.59,79,3.22,3.47,0.004),
    ('2015-10-20 08:41:00',48,9.99,79,5.85,3.60,0.005),
    ('2015-10-20 08:51:00',47,9.81,79,3.14,3.50,0.004),
    ('2015-10-20 09:01:00',47,9.80,80,1.76,3.64,0.004),
    ('2015-10-20 09:11:00',48,9.82,80,0.43,3.21,0.004),
    ('2015-10-20 09:21:00',49,9.90,80,1.77,3.42,0.004),
    ('2015-10-20 09:31:00',49,9.87,80,0.17,3.17,0.003),
    ('2015-10-20 09:41:00',51,10.21,80,3.96,3.72,0.004),
    ('2015-10-20 09:51:00',49,9.95,80,3.32,3.35,0.004),
    ('2015-10-20 10:01:00',46,9.72,80,0.27,3.17,0.003),
    ('2015-10-20 10:11:00',48,9.83,80,4.83,3.64,0.003),
    ('2015-10-20 10:21:00',46,9.71,80,1.81,3.08,0.003),
    ('2015-10-20 10:31:00',46,9.75,80,1.64,4.25,0.004),
    ('2015-10-20 10:41:00',55,10.52,80,6.89,3.89,0.004),
    ('2015-10-20 10:51:00',45,9.74,80,4.76,3.55,0.004),
    ('2015-10-20 11:01:00',44,9.63,80,4.75,3.13,0.003),
    ('2015-10-20 11:11:00',41,9.50,80,1.81,3.14,0.004),
    ('2015-10-20 11:21:00',42,9.58,80,1.76,3.42,0.003),
    ('2015-10-20 11:31:00',42,9.51,80,1.74,3.34,0.003),
    ('2015-10-20 11:41:00',46,9.88,80,2.39,3.54,0.004),
    ('2015-10-20 11:51:00',42,9.60,80,0.10,3.22,0.004),
    ('2015-10-20 12:01:00',42,9.53,81,7.09,3.42,0.003),
    ('2015-10-20 12:11:00',41,9.50,81,1.56,3.24,0.005),
    ('2015-10-20 12:21:00',41,9.50,81,0.26,3.37,0.005),
    ('2015-10-20 12:31:00',43,9.58,80,2.56,3.18,0.005),
    ('2015-10-20 12:41:00',47,9.96,81,5.42,3.84,0.006),
    ('2015-10-20 12:51:00',44,9.69,81,0.10,3.54,0.005),
    ('2015-10-20 13:01:00',43,9.55,81,0.32,3.16,0.005),
    ('2015-10-20 13:11:00',44,9.62,81,0.30,3.23,0.005),
    ('2015-10-20 13:21:00',44,9.65,81,0.24,3.22,0.005),
    ('2015-10-20 13:31:00',42,9.53,81,3.35,3.18,0.009),
    ('2015-10-20 13:41:00',46,9.86,81,6.96,4.02,0.007),
    ('2015-10-20 13:51:00',43,9.62,81,0.51,3.61,0.008),
    ('2015-10-20 14:01:00',42,9.54,81,0.28,3.57,0.011),
    ('2015-10-20 14:11:00',42,9.56,81,0.76,3.53,0.008),
    ('2015-10-20 14:21:00',53,10.19,81,1.41,3.61,0.006),
    ('2015-10-20 14:31:00',42,9.53,81,0.20,3.25,0.008),
    ('2015-10-20 14:41:00',46,9.92,81,2.50,4.19,0.009),
    ('2015-10-20 14:51:00',43,9.63,81,0.25,3.40,0.008),
    ('2015-10-20 15:01:00',40,9.46,81,0.25,3.15,0.006),
    ('2015-10-20 15:11:00',41,9.47,81,0.40,3.14,0.006),
    ('2015-10-20 15:21:00',52,10.22,81,0.24,3.67,0.006),
    ('2015-10-20 15:31:00',49,9.93,81,0.13,3.47,0.006),
    ('2015-10-20 15:41:00',57,10.78,81,2.51,4.13,0.010),
    ('2015-10-20 15:51:00',47,9.83,81,0.15,3.64,0.012),
    ('2015-10-20 16:01:00',54,10.25,81,0.24,3.61,0.013),
    ('2015-10-20 16:11:00',45,9.67,81,0.35,3.42,0.008),
    ('2015-10-20 16:21:00',41,9.49,81,0.40,3.22,0.006),
    ('2015-10-20 16:31:00',40,9.44,81,0.19,3.27,0.007),
    ('2015-10-20 16:41:00',45,9.84,81,2.32,4.73,0.093),
    ('2015-10-20 16:51:00',54,10.30,81,0.32,3.83,0.009),
    ('2015-10-20 17:01:00',48,9.91,81,0.27,4.38,0.010),
    ('2015-10-20 17:11:00',53,10.22,81,0.48,3.51,0.007),
    ('2015-10-20 17:21:00',53,10.25,81,0.34,3.30,0.006),
    ('2015-10-20 17:31:00',47,9.78,81,0.15,2.99,0.005),
    ('2015-10-20 17:41:00',48,10.07,81,2.39,4.76,0.006),
    ('2015-10-20 17:51:00',54,10.35,81,0.37,4.72,0.006),
    ('2015-10-20 18:01:00',43,9.58,81,0.25,3.35,0.006),
    ('2015-10-20 18:11:00',41,9.48,81,0.34,3.38,0.006),
    ('2015-10-20 18:21:00',41,9.44,81,0.33,3.14,0.005),
    ('2015-10-20 18:31:00',41,9.47,81,0.24,3.12,0.006),
    ('2015-10-20 18:41:00',47,9.96,81,2.43,3.68,0.006),
    ('2015-10-20 18:51:00',43,9.61,81,2.87,3.23,0.006),
    ('2015-10-20 19:01:00',42,9.55,81,1.83,3.15,0.005),
    ('2015-10-20 19:11:00',41,9.49,81,1.88,3.39,0.005),
    ('2015-10-20 19:21:00',41,9.50,81,0.31,3.13,0.006),
    ('2015-10-20 19:31:00',40,9.44,81,0.58,3.39,0.005),
    ('2015-10-20 19:41:00',47,9.97,81,7.03,4.01,0.009),
    ('2015-10-20 19:51:00',46,9.83,81,0.33,3.38,0.005),
    ('2015-10-20 20:01:00',43,9.57,81,0.27,3.51,0.005),
    ('2015-10-20 20:11:00',45,9.68,81,0.42,3.43,0.005),
    ('2015-10-20 20:21:00',46,9.68,81,0.23,3.20,0.006),
    ('2015-10-20 20:31:00',44,9.60,81,0.13,3.11,0.005),
    ('2015-10-20 20:41:00',48,10.02,81,6.97,3.75,0.006),
    ('2015-10-20 20:51:00',47,9.85,81,0.64,8.53,0.007),
    ('2015-10-20 21:01:00',43,9.53,81,0.21,3.14,0.005),
    ('2015-10-20 21:11:00',44,9.62,81,0.38,3.13,0.005),
    ('2015-10-20 21:21:00',40,9.40,82,0.17,3.39,0.005),
    ('2015-10-20 21:31:00',40,9.38,81,0.16,3.11,0.005),
    ('2015-10-20 21:41:00',46,9.80,81,2.40,3.67,0.005),
    ('2015-10-20 21:51:00',43,9.57,81,1.66,3.35,0.007),
    ('2015-10-20 22:01:00',43,9.52,81,0.29,3.22,0.007),
    ('2015-10-20 22:11:00',41,9.45,82,1.97,5.08,0.005),
    ('2015-10-20 22:21:00',41,9.50,82,1.79,5.80,0.005),
    ('2015-10-20 22:31:00',42,9.52,82,0.21,5.74,0.005),
    ('2015-10-20 22:41:00',49,10.02,82,2.55,5.53,0.006),
    ('2015-10-20 22:51:00',45,9.72,82,0.11,6.57,0.005),
    ('2015-10-20 23:01:00',53,10.29,82,0.23,3.67,0.008),
    ('2015-10-20 23:11:00',51,10.04,82,0.39,6.67,0.005),
    ('2015-10-20 23:21:00',41,9.49,82,0.24,5.46,0.005),
    ('2015-10-20 23:31:00',41,9.46,82,0.15,8.91,0.005),
    ('2015-10-20 23:41:00',44,9.72,83,2.32,7.62,0.006),
    ('2015-10-20 23:51:00',42,9.53,82,0.10,3.22,0.005)
    ;
    
**Query 1**:

    SELECT TOP (10)
          Xminutes
        , Xminutes % 20 as ToDeductMinutes
        , DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101') as "20 Min Intervals"
        , [DateStamp]
    FROM Table1
    CROSS APPLY (
          SELECT DATEDIFF(MINUTE, '20000101', [DateStamp])
          ) CA (Xminutes)
    ORDER BY
          [DateStamp]
    

**[Results][2]**:
    | Xminutes | ToDeductMinutes |          20 Min Intervals |                 DateStamp |
    |----------|-----------------|---------------------------|---------------------------|
    |  8310240 |               0 | October, 20 2015 00:00:00 | October, 20 2015 00:00:00 |
    |  8310250 |              10 | October, 20 2015 00:00:00 | October, 20 2015 00:10:00 |
    |  8310260 |               0 | October, 20 2015 00:20:00 | October, 20 2015 00:20:00 |
    |  8310270 |              10 | October, 20 2015 00:20:00 | October, 20 2015 00:30:00 |
    |  8310280 |               0 | October, 20 2015 00:40:00 | October, 20 2015 00:40:00 |
    |  8310290 |              10 | October, 20 2015 00:40:00 | October, 20 2015 00:50:00 |
    |  8310300 |               0 | October, 20 2015 01:00:00 | October, 20 2015 01:00:00 |
    |  8310310 |              10 | October, 20 2015 01:00:00 | October, 20 2015 01:10:00 |
    |  8310320 |               0 | October, 20 2015 01:20:00 | October, 20 2015 01:20:00 |
    |  8310330 |              10 | October, 20 2015 01:20:00 | October, 20 2015 01:30:00 |
**Query 2**:

    
    
    
    SELECT
          DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101') AS DateStamp
        , SUM(CPUUsage) AS CPUUsage
        , SUM(CPUReady) AS CPUReady
        , SUM(MemoryUsage) AS MemoryUsage
        , SUM(DiskIOPerfRead) AS DiskIOPerfRead
        , SUM(DiskIOPerfWrite) AS DiskIOPerfWrite
        , SUM(NicPerf) AS NicPerf
    FROM table1
    CROSS APPLY (
          SELECT DATEDIFF(MINUTE, '20000101', [DateStamp])
          ) CA (Xminutes)
    GROUP BY
          DATEADD(MINUTE, Xminutes - (Xminutes % 20) , '20000101')
    

**[Results][3]**:
    |                 DateStamp | CPUUsage | CPUReady | MemoryUsage | DiskIOPerfRead | DiskIOPerfWrite | NicPerf |
    |---------------------------|----------|----------|-------------|----------------|-----------------|---------|
    | October, 20 2015 00:00:00 |       90 |       18 |         158 |              2 |              10 |       0 |
    | October, 20 2015 00:20:00 |       87 |       18 |         158 |              1 |              14 |       0 |
    | October, 20 2015 00:40:00 |       94 |       19 |         159 |              3 |               8 |       0 |
    | October, 20 2015 01:00:00 |       93 |       18 |         157 |              0 |               6 |       0 |
    | October, 20 2015 01:20:00 |       96 |       18 |         157 |              0 |               8 |       0 |
    | October, 20 2015 01:40:00 |       99 |       19 |         158 |              3 |              10 |       0 |
    | October, 20 2015 02:00:00 |       95 |       18 |         158 |              2 |              18 |       0 |
    | October, 20 2015 02:20:00 |       93 |       18 |         159 |              1 |              16 |       0 |
    | October, 20 2015 02:40:00 |       95 |       19 |         160 |              3 |               8 |       0 |
    | October, 20 2015 03:00:00 |       89 |       18 |         158 |              0 |              10 |       0 |
    | October, 20 2015 03:20:00 |       87 |       18 |         158 |              0 |               7 |       0 |
    | October, 20 2015 03:40:00 |       92 |       19 |         158 |              4 |              10 |       0 |
    | October, 20 2015 04:00:00 |       87 |       19 |         158 |              0 |               6 |       0 |
    | October, 20 2015 04:20:00 |       86 |       18 |         158 |              6 |               6 |       0 |
    | October, 20 2015 04:40:00 |       89 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 05:00:00 |       84 |       18 |         158 |              0 |               6 |       0 |
    | October, 20 2015 05:20:00 |       83 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 05:40:00 |       89 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 06:00:00 |       84 |       18 |         158 |              0 |               6 |       0 |
    | October, 20 2015 06:20:00 |       89 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 06:40:00 |       85 |       18 |         158 |              5 |               6 |       0 |
    | October, 20 2015 07:00:00 |       81 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 07:20:00 |       83 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 07:40:00 |       90 |       18 |         158 |              7 |               6 |       0 |
    | October, 20 2015 08:00:00 |       87 |       18 |         158 |              2 |               6 |       0 |
    | October, 20 2015 08:20:00 |       90 |       18 |         158 |              4 |               6 |       0 |
    | October, 20 2015 08:40:00 |       95 |       18 |         158 |              9 |               6 |       0 |
    | October, 20 2015 09:00:00 |       95 |       18 |         160 |              2 |               6 |       0 |
    | October, 20 2015 09:20:00 |       98 |       18 |         160 |              2 |               6 |       0 |
    | October, 20 2015 09:40:00 |      100 |       19 |         160 |              7 |               6 |       0 |
    | October, 20 2015 10:00:00 |       94 |       18 |         160 |              5 |               6 |       0 |
    | October, 20 2015 10:20:00 |       92 |       18 |         160 |              4 |               7 |       0 |
    | October, 20 2015 10:40:00 |      100 |       19 |         160 |             12 |               6 |       0 |
    | October, 20 2015 11:00:00 |       85 |       18 |         160 |              7 |               6 |       0 |
    | October, 20 2015 11:20:00 |       84 |       18 |         160 |              4 |               6 |       0 |
    | October, 20 2015 11:40:00 |       88 |       18 |         160 |              2 |               6 |       0 |
    | October, 20 2015 12:00:00 |       83 |       18 |         162 |              9 |               6 |       0 |
    | October, 20 2015 12:20:00 |       84 |       18 |         161 |              3 |               6 |       0 |
    | October, 20 2015 12:40:00 |       91 |       18 |         162 |              5 |               6 |       0 |
    | October, 20 2015 13:00:00 |       87 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 13:20:00 |       86 |       18 |         162 |              3 |               6 |       0 |
    | October, 20 2015 13:40:00 |       89 |       18 |         162 |              8 |               7 |       0 |
    | October, 20 2015 14:00:00 |       84 |       18 |         162 |              1 |               6 |       0 |
    | October, 20 2015 14:20:00 |       95 |       19 |         162 |              1 |               6 |       0 |
    | October, 20 2015 14:40:00 |       89 |       18 |         162 |              3 |               7 |       0 |
    | October, 20 2015 15:00:00 |       81 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 15:20:00 |      101 |       19 |         162 |              0 |               6 |       0 |
    | October, 20 2015 15:40:00 |      104 |       19 |         162 |              3 |               7 |       0 |
    | October, 20 2015 16:00:00 |       99 |       19 |         162 |              0 |               6 |       0 |
    | October, 20 2015 16:20:00 |       81 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 16:40:00 |       99 |       19 |         162 |              2 |               7 |       0 |
    | October, 20 2015 17:00:00 |      101 |       19 |         162 |              0 |               7 |       0 |
    | October, 20 2015 17:20:00 |      100 |       19 |         162 |              0 |               5 |       0 |
    | October, 20 2015 17:40:00 |      102 |       20 |         162 |              2 |               8 |       0 |
    | October, 20 2015 18:00:00 |       84 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 18:20:00 |       82 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 18:40:00 |       90 |       18 |         162 |              5 |               6 |       0 |
    | October, 20 2015 19:00:00 |       83 |       18 |         162 |              4 |               6 |       0 |
    | October, 20 2015 19:20:00 |       81 |       18 |         162 |              1 |               6 |       0 |
    | October, 20 2015 19:40:00 |       93 |       18 |         162 |              7 |               7 |       0 |
    | October, 20 2015 20:00:00 |       88 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 20:20:00 |       90 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 20:40:00 |       95 |       19 |         162 |              8 |              11 |       0 |
    | October, 20 2015 21:00:00 |       87 |       18 |         162 |              0 |               6 |       0 |
    | October, 20 2015 21:20:00 |       80 |       18 |         163 |              0 |               6 |       0 |
    | October, 20 2015 21:40:00 |       89 |       18 |         162 |              4 |               6 |       0 |
    | October, 20 2015 22:00:00 |       84 |       18 |         163 |              2 |               8 |       0 |
    | October, 20 2015 22:20:00 |       83 |       18 |         164 |              2 |              10 |       0 |
    | October, 20 2015 22:40:00 |       94 |       19 |         164 |              3 |              11 |       0 |
    | October, 20 2015 23:00:00 |      104 |       20 |         164 |              0 |               9 |       0 |
    | October, 20 2015 23:20:00 |       82 |       18 |         164 |              0 |              13 |       0 |
    | October, 20 2015 23:40:00 |       86 |       18 |         165 |              2 |              10 |       0 |

  [1]: http://sqlfiddle.com/#!6/92a59/8
  [2]: http://sqlfiddle.com/#!6/92a59/8/0
  [3]: http://sqlfiddle.com/#!6/92a59/8/1

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mauro CazabonnetSenior .NET Software EngineerAuthor Commented:
Thx,
This is what i ended up with

Select DateAdd(Minute, @Interval * (DateDiff(Minute, 0, DateStamp) / @Interval), 0) as DateStamp, AVG(CPUUsage) as CPUUsage, AVG(CPUReady) as CPUReady,
 AVG(MemoryUsage) as MemoryUsage, AVG(DiskIOPerfRead) as DiskIOPerfRead, AVG(DiskIOPerfWrite) as DiskIOPerfWrite, AVG(NicPerf) as NicPerf
FROM [dbo].[ESXHosts]
WHERE (CONVERT(DATE,DATEStamp,101) >= @startdate) AND (CONVERT(Date,DATEStamp,101) <= @enddate)
Group By DateAdd(Minute, @Interval * (DateDiff(Minute, 0, DateStamp) / @Interval), 0)

Thx again!!
PortletPaulEE Topic AdvisorCommented:
Great!

Just a small follow on note:

WHERE (CONVERT(date, DATEStamp, 101) >= @startdate)
      AND (CONVERT(date, DATEStamp, 101) <= @enddate)

There is no need to convert that column to do the filtering. It is way more efficient to leave the data "as is" and to adjust the end date instead e.g.

WHERE DATEStamp >= @startdate
      AND DATEStamp < DATEADD(DAY,1,@enddate)

note the second line uses only less than
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.