SQL Server : count number of rows per hour

1

votes

I have a table with 2628 rows in it. I want to get a count per hour. I have a completion_time column which tells the date time of per record.

I can find only one-hour count.

select count(*) 
from billing b 
where b.completion_time >= '2019-04-16 23:50:23' 
  and b.completion_time <='2019-04-17 00:50:22'

The date time is up to 9 hours. i.e. the process was started at 2016-04-16 23:50:23 and it ends on 2019-04-17 08:16:49. So I want total counts per hour.

Update 1

I want output like below

How can I achieve it? Any help would be highly appreciated.

sqlsql-servercount

Have you tried any group by clause? – qxg

@qxg group by is giving pe records per minutes – Moeez

the 1st hour, 2nd hour etc is based on which column and with relative to what time ? – Squirrel

4

votes

Try this:

select datepart(hour,b.completion_time) Hour, count(*) NumberOfRecords
from billing b
group by datepart(hour,b.completion_time)

Edit:

select  row_number() over (order by min(b.completion_time)) RowNumber, count(*) NumberOfRecords
from billing b
group by datepart(hour,b.completion_time)
order by min(b.completion_time)

0

votes

try this.

    Declare @StartTime DATETIME = '2016-04-16 23:50:23'
    Declare @EndTime DateTime = '2019-04-17 08:16:49'

    Declare @Tab Table(id int, Completion_Time DateTime)

    Insert into @Tab
    SELECT 1, '2016-04-16 23:50:23' Union All
    SELECT 2,'2016-04-17 00:50:24' Union All
    SELECT 3,'2016-04-17 01:50:26' Union All
    SELECT 4,'2016-04-17 01:50:32' Union All
    SELECT 5,'2016-04-17 01:50:55' Union All
    SELECT 6,'2016-04-17 02:50:28' Union All
    SELECT 7,'2016-04-17 02:50:30' Union All
    SELECT 8,'2016-04-17 02:50:45' Union All
    SELECT 9,'2016-04-17 04:50:32' Union All
    SELECT 10,'2016-04-17 04:50:52' 

    --Select Id, DATEDIFF(HH,@StartTime,Completion_Time) Diff from @Tab

    ;with cte
    As
    (

        SELECT CONVERT(VARCHAR(5),DATEDIFF(HH,@StartTime,Completion_Time)+1) as [Hour] , COUNT(*) As [Records]
        From @Tab
        Group by DATEDIFF(HH,@StartTime,Completion_Time)+1
    )

    Select  [Hour] + CASE
                WHEN [Hour] % 100 IN (11,12,13) THEN 'th'
                WHEN [Hour] % 10 = 1 THEN 'st'
                WHEN [Hour] % 10 = 2 THEN 'nd'
                WHEN [Hour] % 10 = 3 THEN 'rd'
                ELSE 'th' 
            END + ' hour',
        Records
    from cte

-1

votes

This will do just what you need :

 SELECT HOUR(b.completion_time) hourOfDay, COUNT(*) NumberOfRecords
 FROM billing b 
 GROUP BY hourOfDay

HOUR is a predefined function to calculate hour from a DateTime.

SQL Server : count number of rows per hour

3 Answers