Grouping counts by hour (from-to)

Question

I have a problem with query that has to count rows and return numbers as a result. The point is that I need data from recent 24 hours, divided by one hour period. I've done this query by using UNION ALL 24 times but there are more than 800 lines of SQL single query (surprisingly it takes only 3 seconds). I know that I can just group by time somehow but no idea how to do it correctly. I strongly believe that equal query could be just in about 20-30 lines of SQL. I will appreciate for any clues. Here you have some simplified queries of those I mentioned.

Long query (using UNION ALL):

DECLARE @CurrentTime datetime = GETDATE();

--Data from 1 hour
SELECT
----Time
(SELECT CONVERT(VARCHAR(5), (DATEADD(HOUR, -1, @CurrentTime)), 114)) AS [Time],

----Count on 1st table
(SELECT COUNT(T1.[TableFirstId])
FROM [dbo].[Table1] AS T1
WHERE T1.[IncomingDate] BETWEEN (DATEADD(HOUR, -1, @CurrentTime)) AND @CurrentTime) AS [CountT1],
----Count on 2nd table
(SELECT COUNT(T2.[TableSecondId])
FROM [dbo].[Table2] AS T2
WHERE T2.[IncomingDate] BETWEEN (DATEADD(HOUR, -1, @CurrentTime)) AND @CurrentTime) AS [CountT2],
----Count on 3rd table
(SELECT COUNT(T3.[TableThirdId])
FROM [dbo].[Table3] AS T3
WHERE T3.[IncomingDate] BETWEEN (DATEADD(HOUR, -1, @CurrentTime)) AND @CurrentTime) AS [CountT3]

UNION ALL

--Data from 2 hours
SELECT
----Time
(SELECT CONVERT(VARCHAR(5), (DATEADD(HOUR, -2, @CurrentTime)), 114)) AS [Time],

----Count on 1st table
(SELECT COUNT(T1.[TableFirstId])
FROM [dbo].[Table1] AS T1
WHERE T1.[IncomingDate] BETWEEN (DATEADD(HOUR, -2, @CurrentTime)) AND (DATEADD(HOUR, -1, @CurrentTime))) AS [CountT1],
----Count on 2nd table
(SELECT COUNT(T2.[TableSecondId])
FROM [dbo].[Table2] AS T2
WHERE T2.[IncomingDate] BETWEEN (DATEADD(HOUR, -2, @CurrentTime)) AND (DATEADD(HOUR, -1, @CurrentTime))) AS [CountT2],
----Count on 3rd table
(SELECT COUNT(T3.[TableThirdId])
FROM [dbo].[Table3] AS T3
WHERE T3.[IncomingDate] BETWEEN (DATEADD(HOUR, -2, @CurrentTime)) AND (DATEADD(HOUR, -1, @CurrentTime))) AS [CountT3]

UNION ALL

--Data from 3 hours
SELECT
----Time
(SELECT CONVERT(VARCHAR(5), (DATEADD(HOUR, -3, @CurrentTime)), 114)) AS [Time],

----Count on 1st table
(SELECT COUNT(T1.[TableFirstId])
FROM [dbo].[Table1] AS T1
WHERE T1.[IncomingDate] BETWEEN (DATEADD(HOUR, -3, @CurrentTime)) AND (DATEADD(HOUR, -2, @CurrentTime))) AS [CountT1],
----Count on 2nd table
(SELECT COUNT(T2.[TableSecondId])
FROM [dbo].[Table2] AS T2
WHERE T2.[IncomingDate] BETWEEN (DATEADD(HOUR, -3, @CurrentTime)) AND (DATEADD(HOUR, -2, @CurrentTime))) AS [CountT2],
----Count on 3rd table
(SELECT COUNT(T3.[TableThirdId])
FROM [dbo].[Table3] AS T3
WHERE T3.[IncomingDate] BETWEEN (DATEADD(HOUR, -3, @CurrentTime)) AND (DATEADD(HOUR, -2, @CurrentTime))) AS [CountT3]

UNION ALL

--(etc...)

That query gives me something like that:

Time    |   CountT1   |   CountT2   |   CountT3

21:05   |   3215467   |   3456364   |   3234234

20:05   |   2253221   |   3123123   |   3238291

19:05   |   1231467   |    1232342  |   1123123

18:05   |   3112412   |    6712353  |   1233124

17:05   |   1242141   |   1241142   |   4112426

16:05   |   3123467   |   3456364   |   3234234

15:05   |   3215467   |   3412334   |   3231234

14:05   |   3324467   |   3456123   |   2312334

13:05   |   3215467   |   3456364   |   1112310

12:05   |   3215467   |   3456364   |   1231234

11:05   |   3123127   |   3456364   |   3234234

10:05   |   3215467   |   3456364   |   3234234

09:05   |   3215467   |   3456364   |   3234234

08:05   |   3215467   |   3456364   |   3234234

07:05   |   3215467   |   3456364   |   3234234

06:05   |   3215467   |   3456364   |   3234234

05:05   |   3215467   |   2212214   |   3234234

04:05   |   3215467   |   3126542   |   3234234

03:05   |   3215467   |   3123364   |   3234234

02:05   |   3215467   |   3456364   |   3234234

01:05   |   3215467   |   3456364   |   3234234

00:05   |   3215467   |   3456364   |   3123123

23:05   |   3215467   |   3456364   |   3212313

22:05   |   3223424   |   1232163   |   1235321

I need the same results be returned by simpler query (thought about something like this):

DECLARE @CurrentTime datetime = GETDATE();

--Data from 24 hours
SELECT

----Count on 1st table
(SELECT COUNT(T1.[TableFirstId])
FROM [dbo].[Table1] AS T1
WHERE T1.[IncomingDate] BETWEEN (DATEADD(HOUR, -24, @CurrentTime)) AND @CurrentTime) AS [CountT1],
----Count on 2nd table
(SELECT COUNT(T2.[TableSecondId])
FROM [dbo].[Table2] AS T2
WHERE T2.[IncomingDate] BETWEEN (DATEADD(HOUR, -24, @CurrentTime)) AND @CurrentTime) AS [CountT2],
----Count on 3rd table
(SELECT COUNT(T3.[TableThirdId])
FROM [dbo].[Table3] AS T3
WHERE T3.[IncomingDate] BETWEEN (DATEADD(HOUR, -24, @CurrentTime)) AND @CurrentTime) AS [CountT3]

FROM [dbo].[Table1] AS T1
GROUP BY DATEPART(HOUR, T1.[IncomingDate])

But it doesn't work as I expected. Is there anyone who is going to help me understand that? And resolve my problem?

We dont have your table to test. Can you tell how does it work different? Does it return 1 row? — Kuzgun, Sep 11 '14 at 20:19
The last query returnes 24 rows which are the same, duplicated row (only counted numbers from 24 hours). — qwert_, Sep 12 '14 at 10:34

score 1 · Answer 1 · edited May 23 '17 at 12:33

This solution revolves around truncating IncomingDate down to the hourly increment you need, and then joining subqueries on that value. I am modifying your requirements a little bit and requiring that all hour increments begin on the hour exactly, as this greatly simplifies the query. I truncate using

CONVERT(VARCHAR(13), IncomingDate, 120)

There are other ways to do so (highlighted here) but this one seems like a better idea if we're gonna group on that value. To get the hourly row count for one table, we have a query like this:

SELECT COUNT(*) AS T1Count,
CONVERT(VARCHAR(13), IncomingDate, 120) AS IncomingDate
FROM Table1 
GROUP BY CONVERT(VARCHAR(13), IncomingDate, 120)

And now all we need to do is repeat for all 3 tables, and then FULL OUTER JOIN on T1.IncomingDate. The result looks like this:

SELECT T1.IncomingDate, t1.T1Count, t2.T2Count, t3.T3Count FROM
    (SELECT COUNT(*) AS T1Count,
    CONVERT(VARCHAR(13), IncomingDate, 120) as IncomingDate
    from Table1 
    group by CONVERT(VARCHAR(13), IncomingDate, 120)) As T1
FULL OUTER JOIN (SELECT COUNT(*) AS T2Count,
                 CONVERT(VARCHAR(13), IncomingDate, 120) as IncomingDate
                 from Table2 
                 group by CONVERT(VARCHAR(13), IncomingDate, 120)) As T2
    ON T1.IncomingDate = T2.IncomingDate
FULL OUTER JOIN (SELECT COUNT(*) AS T3Count,
                 CONVERT(VARCHAR(13), IncomingDate, 120) as IncomingDate
                 from Table3 
                 group by CONVERT(VARCHAR(13), IncomingDate, 120)) As T3
    ON T1.IncomingDate = T3.IncomingDate
order by T1.IncomingDate

Note that

This query will be for ALL YOUR DATA, I hope your server can handle it. You can easily trim it down with a few WHERE clauses.
I could not test the 3 way full outer join on my local machine without a lot of test data, which I was too lazy to create myself. If it doesn't you can use this answer for a better 3 way outer join.

Yes, you are right. That is the method for grouping by hour a simple SELECT query. I need to group inside subqueries and if I am right it is forbidden in subqueries (because subquery has to return only one column and GROUP BY need another one so that is why it is impossible in subqueries if I am right). — qwert_, Sep 13 '14 at 18:26
@JakubM I think I got a better understanding of your requirement. I completely overhauled the answer. Let me know if it works. — Guillaume CR, Sep 16 '14 at 15:24
Thank you so much for your commitment. To be honest I found out a solution to my problem in meantime, but probably it is not the best way. I built query which is based on temporary tables (I was able to use GROUP BY inside them). My architect said that I have to slightly improve my query, so I need to analyze your proposition. At the moment I am working on test DB (there are not so many records), but I have no idea how the query will run on prod DB. I will check your query for sure but I need some time. Thank you very much one more time. I will paste there the query I built for others. — qwert_, Sep 17 '14 at 20:28

score 1 · Answer 2 · answered Sep 12 '14 at 01:59

This query would give you exact same results as your query with less SQL.

DECLARE @HourTbl TABLE ( HourBegin DATETIME, HourEnd DATETIME)
DECLARE @i INT=0
DECLARE @dtBegin datetime 
DECLARE @dtEnd datetime = GETDATE()

WHILE @i < 24
BEGIN
    SET @i = (@i + 1)
    SET @dtBegin = @dtEnd
    SET @dtEnd = DATEADD(HOUR, -@i, @dtBegin)
    INSERT  INTO @HourTbl
    SELECT   @dtBegin, @dtEnd   
END

;with hourVal (  hourBegin , hourEnd )
as
(

select  hourBegin, hourend
from @HourTbl
)
select CONVERT(VARCHAR(5), hourVal.hourBegin, 114), 
(
select  COUNT(T1.[TableFirstId]) 
from Table1
where T1.[IncomingDate] BETWEEN hourVal.hourBegin, hourVal.hourEnd
),
(
select COUNT(T1.[TableFirstId]) 
from Table2
where T1.[IncomingDate] BETWEEN hourVal.hourBegin, hourVal.hourEnd
)

Grouping counts by hour (from-to)

2 Answers2