CODEWITHSUNDEEP
sql-servert-sqlaggregate
Charles Okwuagwu
Charles Okwuagwu

Reputation: 10866

T-SQL to create one data point for each hour over past 24 hours

Please how may we do this:

1) Generate 24 rows one for each hour from current time back 24 hours

2) Aggregate data from another table over the past 24 hours into these 24 data points.

I have seen solutions suggesting number tables from 0-23, but these might make it difficult if we need this to start from NOW, then run back 24 hours Get every hour for a time range

e.g [5:00am, 4:00am, 3:00am ... 12:am, 11pm ... 7:00am,6:00am]

Source Table:

select d,h,count(1)cnt from msgs 
where dt>= DateAdd(hh, -24, sysdatetime()) 
group by d,h order by 1 desc,2 desc

Sample Data

d           h   cnt
2015-06-05  16  11
2015-06-05  13  44
2015-06-05  12  16
2015-06-05  11  31
2015-06-05  10  10
2015-06-05  9   12
2015-06-05  8   1
2015-06-04  21  1
2015-06-04  20  2
2015-06-04  18  5
2015-06-04  16  2

I have missing hours, i would need a query that fills out the missing hours with 0

Upvotes: 1

Views: 1794

Answers (2)

Charles
Charles

Reputation: 342

As an alternative solution, you could use this query to provide all 24 hour ranges. Then simply aggregate and sum these values against your original query to return only 24 rows.

;WITH hrs AS
(
    SELECT h = 1
    UNION ALL
    SELECT h + 1
    FROM hrs
    WHERE h + 1 <= 24
)
SELECT 
    d = left(convert(varchar(50),DateAdd(hour, -1 * h, getdate()), 21),10), 
    h = DatePart(hour, DateAdd(hour, -1 * h, getdate())), 
    cnt = 0
FROM hrs

Upvotes: 2

Martin Brown
Martin Brown

Reputation: 25320

You could try joining to this function:

CREATE FUNCTION ufn_Last24Hrs
(
    @start DateTime2(7)
)
RETURNS @Result TABLE (d char(10), h int)
AS
BEGIN
    DECLARE @current DateTime2(7) = @start

    WHILE (@current > DateAdd(hour, -24, @start))
    BEGIN
        INSERT INTO @Result
        VALUES
        (
            REPLACE(CONVERT(char(10), @current, 102) , '.', '-'),
            DATEPART(hour, @current)
        )

        SET @current = DateAdd(hour, -1, @current)
    END

    RETURN;
END;
GO

SELECT * FROM ufn_Last24Hrs(SYSDATETIME());

SELECT 
    d,h,COUNT(1)cnt 
FROM 
    ufn_Last24Hrs(SYSDATETIME()) hrs
    left join msgs 
        ON msgs.d = hrs.d
        and msgs.h = hrs.h
WHERE dt>= DateAdd(hour, -24, SYSDATETIME()) 
GROUP BY d,h 
ORDER BY 1 DESC, 2 DES

Upvotes: 1

Related Questions