I have a table calls that shows every call for every employee and looks like this:

Now I want to show the call count for each employee for every 30 minutes interval:

The considered period is 08:00 - 08:30 / 08:30 -09:00 and so on.

Is there an easy way to get the desired result?

Thanks in advance.

CodePudding user response：

The way I like to round datetime values to the nearest n-minute interval is to take advantage of SQL Server's integer math behavior. If you take the difference in minutes between midnight and the time in question, then divide by n and then multiply by n, it gets rid of any remainder. So to round right now down to the previous 30-minute interval:

DECLARE @now   datetime = GETDATE();
DECLARE @today datetime = CONVERT(date, @now);

SELECT DATEADD
(
  MINUTE, 
  DATEDIFF(MINUTE, @today, @now)/30*30,
  @today
);

We can apply this to your query by taking your source table and using CROSS APPLY as Charlie suggested and apply that same calculation to your source values (you have to do a little more conversion inline because you don't have nice, static variables to use):

DECLARE @WindowSizeInMinutes smallint = 30;

SELECT x.interval, c.employee, call_count = COUNT(*)
FROM dbo.Calls AS c
CROSS APPLY 
(
  VALUES 
  (
    DATEADD
    (
      MINUTE, 
      DATEDIFF
      (
        MINUTE, 
        CONVERT(datetime, CONVERT(date, call_pick_up_time)), 
        call_pick_up_time
      ) / @WindowSizeInMinutes * @WindowSizeInMinutes,
      CONVERT(datetime, CONVERT(date, call_pick_up_time))
    )
  )
) AS x(interval)
-- WHERE c.something something
GROUP BY c.employee, x.interval;

If there is an index on call_pick_up_time you were hoping to use, that's out the window.

Another approach that could make use of an index is to pre-determine all the possible 30-minute windows in the range you're after, and then inner join to those:

DECLARE @WindowSizeInMinutes smallint = 30,
        @min_date datetime = '20211001', 
        @max_date datetime = '20211014';
    
;WITH n(n) AS
(
  SELECT 0 UNION ALL 
  SELECT n   1
    FROM n WHERE n <= 24*60/@WindowSizeInMinutes
),
days(d) AS
(
  SELECT @min_date UNION ALL
  SELECT DATEADD(DAY, 1, d)
    FROM days WHERE d < @max_date
),
intervals AS 
(
  SELECT interval_start = DATEADD(MINUTE, n*@WindowSizeInMinutes, d),
         interval_end   = DATEADD(MINUTE, (n 1)*@WindowSizeInMinutes, d)
    FROM n CROSS JOIN days
)
SELECT interval = i.interval_start, 
   c.employee, 
   call_count = COUNT(c.employee)
FROM intervals AS i
INNER JOIN dbo.Calls AS c
   ON c.call_pick_up_time >= i.interval_start
  AND c.call_pick_up_time <  i.interval_end
GROUP BY c.employee, i.interval_start;

While more complicated, one nice thing about this approach is if you want to show slots for windows where no employees had calls, you could just change the join to an inner join, and if you wanted a slot for each employee, you could just add a CTE with the list of employees and cross join to that.

• Both examples on this db<>fiddle