I am trying to aggregate the row_date, starttime, and acdcalls so that I can get a sum of calls made in any given 15 minute period for that date.

Doesn't matter if they are in splits 100 thru 107.

Here is where I was able to get to so far:

SQL Query1

DECLARE @@searchdate date = '2022-2-17'
SELECT [row_date]  
  ,[starttime]  
  ,[split]  
  ,[acdcalls]     
FROM [CMS].[dbo].[hsplit]  
WHERE split IN (100,101,102,103,104,105,106,107)  
AND (acdcalls > 0)  
AND (row_date = @@searchdate)  
GROUP BY row_date, starttime, split, acdcalls  
ORDER BY row_date, starttime asc ;

Results

Trying to get it to look like this:

Any help much appreciated!

CodePudding user response：

If the splits don't matter, don't group by them and definitely don't group by the measure you want to aggregate on, just choose the aggregating function. I don't really know what acdcalls represent but if it's the number of calls via automated call distribution then you probably need a SUM:

DECLARE @searchdate date = '20220217'
SELECT [row_date]  
  ,[starttime]  
  ,SUM([acdcalls]) AS acdcalls
FROM [CMS].[dbo].[hsplit]  
WHERE split IN (100,101,102,103,104,105,106,107)  
AND (acdcalls > 0)  
AND (row_date = @searchdate)  
GROUP BY row_date, starttime
ORDER BY row_date, starttime asc ;