CODEWITHSUNDEEP
sql-serversql-server-2008
IamNumber5
IamNumber5

Reputation: 351

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

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

Upvotes: 0

Views: 93

Answers (2)

Pரதீப்
Pரதீப்

Reputation: 93724

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

Upvotes: 1

chrisb
chrisb

Reputation: 2210

--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

Upvotes: 0

Related Questions