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?

Answer 1

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.

Answer 2

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
)