Really struggling with this as a SQL newb, so i need to place values from the is_registered column into hourly buckets based on the time of day they were created. The below is a small sample

The result I would like to acheive (with the full dataset) is the following(buckets for each hour of the day

And so on.

Hope thats as enough information provided, any help would be greatly appreciated, thank you

CodePudding user response：

Try following way

    --Create table script

CREATE TABLE [dbo].[Table_4](
    [creationdate] [datetime] NULL,
    [isreg] [int] NULL
) ON [PRIMARY]
GO

    --Sample data
    
    insert into table_4 values (getdate(),1)
    insert into table_4 values (getdate()-1,1)
    go
    
    
    -- query
    WITH report(N) AS(
        SELECT TOP(23) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM sys.columns
    )
    ,hourly(creationdate) AS(
        SELECT DATEADD(HOUR, t.N, d.creationdate)
        FROM report t
        CROSS JOIN(
            SELECT DISTINCT DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0) AS creationdate FROM table_4
        ) d
    )
    SELECT
       convert(date, h.creationdate) as [Creation date],
       convert(varchar(5), DATEPART(HOUR, h.creationdate)-1)  ' - '   convert(varchar(5),DATEPART(HOUR, h.creationdate)) as [Hour Bucket], 
        [Total in each bucket] = ISNULL(t.isreg, 0)
    FROM hourly h
    LEFT JOIN(
        SELECT
            creationdate = DATEADD(HOUR, DATEPART(HOUR, creationdate) ,DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0)),
            isreg = COUNT(*)
        FROM table_4
        GROUP BY DATEADD(DD, DATEDIFF(DD, 0, creationdate), 0), DATEPART(HOUR, creationdate)
    )t
        ON t.creationdate = h.creationdate
        order by h.creationdate

Reference link

CodePudding user response：

Using this tvf may be more maintainable: DateRange TVF

 WITH Ranges
 AS
 (
    SELECT [Value] AS date_from
        ,LEAD([Value]) OVER (ORDER BY [Value]) AS date_to
        ,CAST([Value] AS date) AS [Date]
        ,CONVERT(char(5), [Value], 14)   '-'
              CONVERT(char(5), LEAD([Value]) OVER (ORDER BY [Value]), 14) AS Hour_Bucket
    FROM dbo.DateRange('2021-10-28', '2021-10-30', 'hh', 1)
)
SELECT R.[Date], R.Hour_Bucket
    ,COUNT(T.is_registered) AS Total_Registered
FROM Ranges R
    LEFT JOIN YourTable T
        ON T.creation_date >= R.date_from
            AND T.creation_date < R.date_to
            AND T.is_registered = 1
WHERE R.date_to IS NOT NULL
GROUP BY R.[Date], R.Hour_Bucket
ORDER BY [Date], Hour_Bucket;