T-SQL如何按30分钟间隔统计calls表中每位员工的通话数量

以下是最简实现代码：

SELECT
    CONCAT(
        FORMAT(DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(call_pick_up_time AS DATE), call_pick_up_time) / 30 * 30, CAST(call_pick_up_time AS DATE)), 'HH:mm'),
        ' - ',
        FORMAT(DATEADD(MINUTE, (DATEDIFF(MINUTE, CAST(call_pick_up_time AS DATE), call_pick_up_time) / 30 * 30) + 30, CAST(call_pick_up_time AS DATE)), 'HH:mm')
    ) AS [interval],
    employee,
    COUNT(*) AS call_count
FROM calls
GROUP BY
    CAST(call_pick_up_time AS DATE),
    DATEDIFF(MINUTE, CAST(call_pick_up_time AS DATE), call_pick_up_time) / 30,
    employee
ORDER BY
    CAST(call_pick_up_time AS DATE),
    [interval],
    employee

逻辑说明 #

• 核心计算逻辑：通过DATEDIFF计算通话时间距离当日0点的总分钟数，除以30取整后再乘30，即可对齐到对应30分钟间隔的起始时间，加30分钟即为间隔结束时间
• 默认按天拆分时段统计，如果你需要统计所有日期同一时段的总通话量，删掉GROUP BY和ORDER BY里的CAST(call_pick_up_time AS DATE)即可
• 可按需在FROM后加WHERE条件过滤数据，比如只统计8:00-20:00的通话，添加WHERE DATEPART(HOUR, call_pick_up_time) BETWEEN 8 AND 19即可

低版本SQL Server兼容方案（不支持FORMAT函数）：
把interval的计算逻辑替换为以下写法即可：

CONVERT(VARCHAR(5), DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(call_pick_up_time AS DATE), call_pick_up_time) / 30 * 30, CAST(call_pick_up_time AS DATE)), 108)
+ ' - ' +
CONVERT(VARCHAR(5), DATEADD(MINUTE, (DATEDIFF(MINUTE, CAST(call_pick_up_time AS DATE), call_pick_up_time) / 30 * 30) + 30, CAST(call_pick_up_time AS DATE)), 108)

内容的提问来源于stack exchange，提问作者NewbieSQL_Germany