How to get a list of total number of loggedin users in every hour?

Question

I am trying to get the total number of "ID User" that was logged in every hour! but what is happening sometime there is no users were loggedin in particular hour and I wanted to show that hour there is Zero users was loggedin. I think to solve this I should of using a join table with all the hours that are between those two date that i am filtering by. any suggestion for this problem?

SELECT FORMAT(chDate,'yyyy-MM-dd H:00:00') as cDate , count(ID) as nValue FROM myDB.myTable Where chDate > '2014-11-03 00:00:00' and chDate < '2014-11-03 4:00:00' Group by FORMAT(chDate,'yyyy-MM-dd H:00:00')

The resault I got

 cDate nValue 2014-11-03 0:00:00 5 2014-11-03 3:00:00 8

The resault I want

 cDate nValue 2014-11-03 0:00:00 5 2014-11-03 1:00:00 0 2014-11-03 2:00:00 0 2014-11-03 3:00:00 8

'join table with all the hours' is probably best solution. You could use two tables - dates table (over some long period, we are using 30 years) and 24 records containing hour table. — Arvo
– Arvo, Commented Nov 4, 2014 at 9:10
I agree with you, but How i could use dates table that is what i am looking for .. do you have any example? I wanted a list of hours bettween two dates. — IamNumber5
– IamNumber5, Commented Nov 4, 2014 at 9:21

Pரதீப் · Accepted Answer · 2014-11-04 10:33:16Z

Try this.

;WITH cte AS (SELECT convert(datetime,'2014-11-03 00:00:00') dates UNION ALL SELECT Dateadd(hh, 1, dates) dates FROM cte WHERE dates < dateadd(hh,-1,'2014-11-03 4:00:00')) SELECT a.dates, Count(id) FROM cte a LEFT JOIN myDB.myTable b ON convert(date,a.dates) = convert(date,b.dates) and DATEPART(hour,a.dates) = DATEPART(hour,b.dates) GROUP BY a.dates

What is #temp is it compatible with Sql server 2008 r2? I got Invalid object name '#temp'.

Community · Accepted Answer · 2018-11-13 22:55:47Z

--example data create table myTable (ID int, chDate datetime) insert into myTable (ID, chDate) values (1, '2014-11-03 01:00'), (1, '2014-11-03 01:30'), (1, '2014-11-03 03:00'), (1, '2014-11-03 05:00') SELECT DATEADD(hour, n.N, 0) as cDate , COUNT(t.ID) as nValue FROM dbo.Numbers n LEFT OUTER JOIN myTable t ON n.N = DATEDIFF(hour, 0, t.chDate) WHERE n.N >= DATEDIFF(hour, 0, '2014-11-03 00:00:00') and n.N < DATEDIFF(hour, 0, '2014-11-03 4:00:00') GROUP BY n.N ORDER BY n.N

Note that this uses a numbers table but a date table would work just as well (better, even)

Example result:

2014-11-03 00:00:00.000 0 2014-11-03 01:00:00.000 2 2014-11-03 02:00:00.000 0 2014-11-03 03:00:00.000 1

Collectives™ on Stack Overflow

How to get a list of total number of loggedin users in every hour?

2 Answers 2

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Related