CODEWITHSUNDEEP
sql-servert-sql
Tart
Tart

Reputation: 305

Count number of occurrences per hour between two dates SQL Server

I've created a temp table #MB that has a record ID (119 rows), start and end date (partial screenshot of the list is below):

enter image description here

I'm trying to get a count of occurrences that happened each hour for each record ID during the start and end date (or number of occurrences each hour when ID was active between two dates).

I've used this code:

SELECT *  
FROM  
    (SELECT  
         ISNULL(CAST(part AS VARCHAR(5)), 'Total') AS part,
         COUNT(*) AS part_count
     FROM    
         (SELECT DATEPART([HOUR], [Start]) AS part
          FROM #MB) grp
     GROUP BY
         GROUPING SETS((part),())
    ) pre
PIVOT   
    (MAX(part_count) 
         FOR part IN ([0], [1], [2], [3], [4], [5], [6], [7], [8],
                      [9], [10], [11], [12], [13], [14], [15], [16],
                      [17], [18], [19], [20], [21], [22], [23], Total)
    ) pvt;

but it counts only records based on the start date (don't count each hour between two dates) and I stuck on how to generate occurrences per hour for each ID between two dates that I can later use to pre-aggregate and pivot.

enter image description here

Upvotes: 1

Views: 1365

Answers (1)

Squirrel
Squirrel

Reputation: 24793

first, you need to generate the list of rows for each hour

here i am using a recursive cte query to do it

; with MB as
(
    select  ID, [Start], [End], [Date] = [Start]
    from    #MB

    union all

    select  ID, [Start], [End], [Date] = dateadd(hour, 1, convert(date, c.[Date]))
    from    MB c
    where   dateadd(hour, 1, c.[Date])  < [End]
)
select  *
from    MB

so in your pivot query , just change to this

; with MB as
(
    select  ID, [Start], [End], [Date] = [Start]
    from    #MB

    union all

    select  ID, [Start], [End], [Date] = DATEADD(HH,DATEPART(HH,[Start]),CAST(CAST([Start] AS DATE) AS DATETIME))
    from    MB c
    where   dateadd(hour, 1, c.[Date])  < [End]
)
SELECT  *  
FROM    (
            SELECT  ISNULL(CAST(part AS VARCHAR(5)), 'Total')   AS part,
                    COUNT(*)            AS part_count
            FROM    (
                        SELECT  DATEPART([HOUR], [Date]) AS part
                        FROM    MB   -- changed to the cte
                    ) grp
            GROUP BY
                    GROUPING SETS((part),())
        ) pre
PIVOT   (MAX(part_count) FOR part IN (
            [0],[1],[2],[3],[4],[5],[6],[7],[8],
            [9],[10],[11],[12],[13],[14],[15],[16],
            [17],[18],[19],[20],[21],[22],[23], Total)) pvt;

Upvotes: 1

Related Questions