Start by counting the orders for each server for each minute:
DECLARE @day datetime = '2024-02-21';
SELECT
c.line As SERVER,
M.Minute,
COUNT(DISTINCT c.id) As OrderCount
FROM
customer c
INNER JOIN orders o ON o.id = c.id
INNER JOIN handle h ON h.line = c.line
CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute)
WHERE
c.datetime >= @day And c.datetime < DateAdd(day, 1, @day)
GROUP BY
c.line,
M.Minute
ORDER BY
c.line,
M.Minute
;
If you really want a column for each minute, then you'll need to use a
PIVOT
- this will still require you to add an entry for each minute:
Using PIVOT and UNPIVOT - SQL Server | Microsoft Learn[
^]
DECLARE @day datetime = '2024-02-21';
WITH cteSource As
(
SELECT
c.line As SERVER,
M.Minute,
COUNT(DISTINCT c.id) As OrderCount
FROM
customer c
INNER JOIN orders o ON o.id = c.id
INNER JOIN handle h ON h.line = c.line
CROSS APPLY (SELECT FORMAT(c.datetime, 'HH:mm')) As M (Minute)
WHERE
c.datetime >= @day And c.datetime < DateAdd(day, 1, @day)
GROUP BY
c.line,
M.Minute
)
SELECT
SERVER,
[09:40],
[09:41],
[09:42],
[09:43],
[09:44],
[09:45]
FROM
cteSource As S
PIVOT
(
SUM(OrderCount)
FOR Minute In ([09:40], [09:41], [09:42], [09:43], [09:44], [09:45])
) As P
ORDER BY
SERVER
;
If you want to avoid typing out all of the minutes, then you'll need a dynamic pivot - for example:
DECLARE @day datetime = '2024-02-21';
DECLARE @columns nvarchar(max);
DECLARE @sql nvarchar(max);
WITH cteHours As
(
SELECT TOP 24 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As H
FROM sys.all_columns
),
cteMinutes As
(
SELECT TOP 60 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 As M
FROM sys.all_columns
)
SELECT
@columns = STUFF(
(SELECT N', [' + FORMAT(H.H, '00') + N':' + FORMAT(M.M, '00') + N']'
FROM cteHours H CROSS APPLY cteMinutes M
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),
1, 1, N'')
;
SET @sql = N'
WITH cteSource As
(
SELECT
c.line As SERVER,
M.Minute,
COUNT(DISTINCT c.id) As OrderCount
FROM
customer c
INNER JOIN orders o ON o.id = c.id
INNER JOIN handle h ON h.line = c.line
CROSS APPLY (SELECT FORMAT(c.datetime, ''HH:mm'')) As M (Minute)
WHERE
c.datetime >= @day And c.datetime < DateAdd(day, 1, @day)
GROUP BY
c.line,
M.Minute
)
SELECT
SERVER, ' + @columns + N'
FROM
cteSource As S
PIVOT
(
SUM(OrderCount)
FOR Minute In (' + @columns + N')
) As P
ORDER BY
SERVER
;';
EXEC sp_executesql @sql, N'@day datetime', @day = @day;