CODEWITHSUNDEEP
sqlsql-servert-sql
DarthCSharper
DarthCSharper

Reputation: 133

Group count for every hour on table

I need to count number of application in table in last N days on every hour. I am using SQL Server 2014. Something like :

select count(*) from [BankLog].[ProcInst]
  where StartDate >= '2019-01-14 09:00:00' and FinishDate <='2019-01-14 10:00:00'

Is there some way to automate to get result like:

date | timeframe | count

Upvotes: 2

Views: 461

Answers (3)

Thom A
Thom A

Reputation: 96038

This is a bit of a stab in the dark, however, a common way to achieve this is by use of a tally table to create all the date increments you need. This should get you on the right path. If you don't understand, please do ask.

USE Sandbox;
GO
DECLARE @StartDate datetime, @EndDate datetime;
SET @StartDate = '20190101';
SET @EndDate = '20190131';

WITH N AS (
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) N(N)),
Tally AS(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS I
    FROM N N1 --10
         CROSS JOIN N N2 --100
         CROSS JOIN N N3 --1000
         CROSS JOIN N N4 --10000 ≈ 1 Year
         CROSS JOIN N N5 --100000 ≈ 11 Years
         CROSS JOIN N N6 --1000000 ≈ 110 Years
    ),
Dates AS(
    SELECT DATEADD(HOUR, T.I, @StartDate) AS StartHour,
           DATEADD(HOUR, T.I+1, @StartDate) AS EndHour
    FROM Tally T
    WHERE DATEADD(HOUR, T.I, @StartDate) < DATEADD(DAY, 1, @EndDate))
SELECT CONVERT(date, StartHour) AS [Date],
       D.StartHour,
       D.EndHour,
       COUNT(BLPI.{Column}) AS [Count] --{Column} needs replacing.
FROM Dates D
     LEFT JOIN [BankLog].[ProcInst] BLPI ON BLPI.StartDate >= D.StartHour
                                        AND BLPI.StartDate < D.EndHour;

Upvotes: 4

Siavash Rostami
Siavash Rostami

Reputation: 1933

This will do the job

SELECT CAST(StartDate as date) AS TheDate,
       DATEPART(hour,StartDate) AS TheHour,
       COUNT(*) AS Totals
FROM [BankLog].[ProcInst]
WHERE StartDate >= '2019-01-14 09:00:00' AND FinishDate <='2019-01-14 10:00:00'
GROUP BY CAST(StartDate as date),
       DATEPART(hour,StartDate)

Upvotes: 4

Armin
Armin

Reputation: 157

You can try to extract the hour with datepart and group it like that.

SELECT CONVERT(VARCHAR(10), yourtable.date 111) AS date
,DATEPART(HH,yourtable.date) AS hour
,COUNT(*) AS count 

FROM yourtable

WHERE yourtable.date > '2019-01-13 09:00:00' 
AND yourtable.date <= '2019-01-14 10:00:00'

GROUP BY 
CONVERT(VARCHAR(10), yourtable.date 111)
,DATEPART(HH,yourtable.date)

ORDER BY 
DATEPART(HH,yourtable.date)

Upvotes: 3

Related Questions