CODEWITHSUNDEEP
sqlt-sqlsql-server-2008-r2
imbrian21
imbrian21

Reputation: 121

SQL Server - Combining results from a single query

Using SQL server 2008R2 I'm attempting to write a query that will return a sum of all values within a specific time frame (in this case in 15min intervals) In the long run this will be used to identify missing data (each server should be returning a value of 12 per 15mins). I've gotten to the point where i get results that im looking for but i need a way to combine them all in the same query. Code Below (date will normally be defined by a variable, using 09/01/2013 as an example in this case):

Select SUM(Accounted_for) 'accounted for',date,time,server
from dbmetrics.dbo.tblmissing 
where DATE='09/01/2013'
  and (TIME between '00:00:00' and '23:59:59')
  and SERVER='Server01'
group by DATE, TIME, server , DATEPART(hour,time),
      DATEPART(MINUTE,time),DATEPART(second,time) / 96
GO

Results return along the lines of this" Results that come back look like this

Number    Date            Time                   Server
4         2013-09-01      12:00:01.0000000      server01
6         2013-09-01      12:00:02.0000000      server01
2         2013-09-01      12:00:15.0000000      server01
10        2013-09-01      12:15:02.0000000      server01
2         2013-09-01      12:15:09.0000000      server01

What I would like to see would be

12      2013-09-01      12:00:00.0000      Server01
12      2013-09-01      12:15:00.0000      Server01

Upvotes: 1

Views: 87

Answers (1)

Kermit
Kermit

Reputation: 34063

You may want to GROUP BY the [Server] rather than get the MAX() of [Server]. As Aaron Bertrand mentions above, it just happens that your time values land on 15 minute intervals. This query will round the time to the minute and group your data as such.

SELECT SUM([Number]) AS [Number], 
       [Date], 
       CONVERT(time, DATEADD(minute, DATEDIFF(minute, 0, [Time]), 0)) AS [Time], 
       MAX([Server]) 
FROM   [Table] 
GROUP  BY [Date], 
          DATEADD(minute, DATEDIFF(minute, 0, [Time]), 0)

See a demo

Upvotes: 1

Related Questions