CODEWITHSUNDEEP
sqlsql-servergroup-bycountwindow-functions
Willy
Willy

Reputation: 10638

SQL Server multiple partitions by month, day and hour

In SQL Server I have a table like below:

processName   initDateTime           
processA      2020-06-15 13:31:15.330
processB      2020-06-20 10:00:30.000
processA      2020-06-20 13:31:15.330
...
and so on

I need to group by processName and for each processName I need to get the number of records by month (#byMonth), day (#byDay) and hour (#byHour).

What is the best way to do it? Something as below? What would be the SQL query?

Possible results:

processName Month    Day  Hour  #byMonth #byDay #byHour #total(by process)
processA    January  15   17    4         3     2       7
processA    January  15   20    4         3     1       7
processA    January  20   05    4         2     3       7
processA    January  20   13    4         2     1       7
processA    March    04   05    3         2     3       7
processA    March    04   17    3         2     2       7
processA    March    15   05    3         3     3       7

...and so on for the rest of processes name

Upvotes: 0

Views: 132

Answers (2)

GMB
GMB

Reputation: 222382

I think that you want aggregation and window functions:

select 
    processName,
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime),
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime)) byMonth,
    sum(count(*)) over(partition by processName, year(initDateTime), month(initDateTime), day(initDateTime)) byDay,
    count(*) byHour
from mytable
group by 
    processName,
    year(initDateTime),
    month(initDateTime),
    day(initDateTime),
    datepart(hour, initDateTime)

Upvotes: 1

GarethD
GarethD

Reputation: 69749

Wherever possible, I like to return dates as dates to the caller, so that they can also treat them as dates for things such as sorting, converting to local time, or even making sure that the language shown is relevant. So if it were me, i would do the following:

-- sample data
CREATE TABLE #T (processName VARCHAR(50), initDateTime  DATETIME)
INSERT #T (processName, initDateTime)
VALUES
    ('processA', '2020-06-15 13:31:15.330'),
    ('processB', '2020-06-20 10:00:30.000'),
    ('processA', '2020-06-20 13:31:15.330')


SELECT  t.processName,
        i.InitHour,
        ByMonth = SUM(COUNT(*)) OVER(PARTITION BY i.InitMonth),
        ByDay = SUM(COUNT(*)) OVER(PARTITION BY i.InitDay),
        ByHour = COUNT(*)
FROM    #T AS t
        CROSS APPLY
        (   SELECT  InitHour = DATEADD(HOUR, DATEDIFF(HOUR, 0, initDateTime), 0), 
                    InitDay = DATEADD(DAY, DATEDIFF(DAY, 0, initDateTime), 0),
                    InitMonth = DATEADD(MONTH, DATEDIFF(MONTH, 0, initDateTime), 0)
        ) AS i
GROUP BY t.processName, i.InitHour, i.InitDay, i.InitMonth;

Which returns:

processName     InitHour                ByMonth ByDay   ByHour
--------------------------------------------------------------
processA        2020-06-15 13:00:00     3       1       1
processA        2020-06-20 13:00:00     3       2       1
processB        2020-06-20 10:00:00     3       2       1

If you need the day number, month name etc in SQL, you can get these using DATEPART or DATENAME, but as above, this is really better handled in the presentation layer, so you can deal with locales, or specific user settings.

Upvotes: 1

Related Questions