How to/is it possible to create a SQL query that can grab 12 columns/rows of monthly data using only-CodePudding

First off, I did not create this database. The table I'm querying does not separate entries into a year-month fashion. It just gets entries dumped into it and each entry has a timestamp.

I'm trying to collect data for the different processes that are logged into this database table for an Excel spreadsheet and then used for presenting purposes.

This is my query currently that I'm using to grab the first 3 months of 2022.

(SELECT
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-01-01') 
            AND CreationTime < CONVERT(datetime, '2022-02-01') 
            AND QueueId = 3)
UNION
(SELECT 
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-02-01') 
            AND CreationTime < CONVERT(datetime, '2022-03-01') 
            AND QueueId = 3)
UNION
(SELECT 
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-03-01') 
            AND CreationTime < CONVERT(datetime, '2022-04-01') 
            AND QueueId = 3)

My issue is that I need the returned query to present the info in the same ordering every time January -> February -> March -> etc.

If I query this today, March is going to be the first row because obviously no processes have ran in March yet and I believe the default ordering is by ascending order. But I can't simply include an "ORDER BY month" at the end of the query because the database does not store which month each entry occurred in...

SQL is definitely not my strongest language so I apologize in advance if this is a bad question, I'm just trying to deal with this assignment that was pushed to me as quickly as possible.

CodePudding user response：

Try UNION ALL instead of UNION. This will prevent the UNION from trying to sort the data as it believes it should. Then you can put each query in the position you want it to be in the results. I.E. January first, then February, etc...

For example...

(SELECT
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-01-01') 
            AND CreationTime < CONVERT(datetime, '2022-02-01') 
            AND QueueId = 3)
UNION ALL
(SELECT 
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-02-01') 
            AND CreationTime < CONVERT(datetime, '2022-03-01') 
            AND QueueId = 3)
UNION ALL
(SELECT 
    COUNT(id) AS Process_1
        FROM QueueItems
            WHERE CreationTime >= CONVERT(datetime, '2022-03-01') 
            AND CreationTime < CONVERT(datetime, '2022-04-01') 
            AND QueueId = 3)

Similarly, you could simplify your query like this...

SELECT 
    DATEPART(month,CreationTime) AS [Month],
    COUNT(id) AS Process_1
FROM QueueItems
WHERE CreationTime >= '2022-01-01'
    AND CreationTime < '2023-01-01'
    AND QueueId = 3
GROUP BY DATEPART(month,CreationTime)
ORDER BY [Month]