How do I get a list of orders by server and by the minute (SQL server)

Question

0.00/5 (No votes)

See more:

Hello-

I am trying to get a list of orders that came across each server by the minute. I am able to get that with a simple script but I have to change the WHERE clause for each minute. I'm hoping to find something that can query the range in one query.

I'm hoping to get an outcome like this....

SERVER	9:40	9:41	9:42	9:43	9:44	9:45
SV1	     9	     12	     7	     8	     1	     2
SV2	     16	     10	     8	     11	     18	     25
SV3	     27	     22	     29	     11	     4	     7
SV4	     22	     20	     22	     22	     47	     47

Thank you !!!

What I have tried:

This is the query that works but I have to change the minute each time. I have to change the 09:40 to 09:41 then 09:42, etc.

SQL

SELECT h.line AS 'SERVER'
,COUNT(DISTINCT c.id) AS '9:40'
FROM customer c 
INNER JOIN orders o 
	ON c.id = o.id
INNER JOIN handle h 
	ON c.line = h.line
WHERE c.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999'
GROUP BY h.line
ORDER BY  1

I have tried this but it takes incredibly long and I still have to change the minutes if I want to query a different range.

SQL

SELECT h.line AS 'SERVER'

,(SELECT COUNT(DISTINCT c1.id) FROM customer c1 INNER JOIN orders o1 ON c1.id = o1.id INNER JOIN handle h1 ON c1.line = h1.line
WHERE h.line = h1.line
AND c1.datetime BETWEEN '2024-02-21 09:40:00.000' AND '2024-02-21 09:40:59.999') AS '9:40'

,(SELECT COUNT(DISTINCT c2.id) FROM customer c2 INNER JOIN orders o2 ON c2.id = o2.id INNER JOIN handle h2 ON c2.line = h2.line
WHERE h.line = h2.line
AND c2.datetime BETWEEN '2024-02-21 09:41:00.000' AND '2024-02-21 09:41:59.999') AS '9:41'

,(SELECT COUNT(DISTINCT c3.id) FROM customer c3 INNER JOIN orders o3 ON c3.id = o3.id INNER JOIN handle h3 ON c3.line = h3.line
WHERE h.line = h3.line
AND c3.datetime BETWEEN '2024-02-21 09:42:00.000' AND '2024-02-21 09:42:59.999') AS '9:42'

,(SELECT COUNT(DISTINCT c4.id) FROM customer c4 INNER JOIN orders o4 ON c4.id = o4.id INNER JOIN handle h4 ON c4.line = h4.line
WHERE h.line = h4.line
AND c4.datetime BETWEEN '2024-02-21 09:43:00.000' AND '2024-02-21 09:43:59.999') AS '9:43'

,(SELECT COUNT(DISTINCT c5.id) FROM customer c5 INNER JOIN orders o5 ON c5.id = o5.id INNER JOIN handle h5 ON c5.line = h5.line
WHERE h.line = h5.line
AND c5.datetime BETWEEN '2024-02-21 09:44:00.000' AND '2024-02-21 09:44:59.999') AS '9:44'

,(SELECT COUNT(DISTINCT c6.id) FROM customer c6 INNER JOIN orders o6 ON c6.id = o6.id INNER JOIN handle h6 ON c6.line = h6.line
WHERE h.line = h6.line
AND c6.datetime BETWEEN '2024-02-21 09:45:00.000' AND '2024-02-21 09:45:59.999') AS '9:45'

FROM customer c 
INNER JOIN orders o 
	ON c.id = o.id
INNER JOIN handle h 
	ON c.line = h.line
GROUP BY h.line
ORDER BY  1

Posted 21-Feb-24 15:39pm

Chris_List

Updated 22-Feb-24 7:01am

Maciej Los

v3

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2024-02-21T22:44:00

Start by counting the orders for each server for each minute:

SQL

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[^]

SQL

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:

SQL

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;