Zero count query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Zero count query...

Author

Topic

paultervit
Starting Member

10 Posts

Posted - 2008-07-23 : 05:51:13

Hello
I have a quick question.
I have a table logging logins and recording dates/times. I want to run a report for every hour including hours where no logins occur. I.E. if noone logged in a count of 0 would be returned. At the moment my query only returns counts for hours where logins took plcae. Can anyone advise on how I do this?

select Month(login_date_time) Month, Day(login_date_time) Day, dateadd(hh,datediff(hh,0,login_date_time),0), count(login_date_time) Logins
from usersession
Where Year(login_date_time) = 2008
and (Month(login_date_time) = 06 or Month(login_date_time) = 07)
group by Month(login_date_time), Day(login_date_time), dateadd(hh,datediff(hh,0,login_date_time),0)
order by Month(login_date_time), Day(login_date_time), dateadd(hh,datediff(hh,0,login_date_time),0)

Hope this makes sense.

Regards

Paul

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 05:53:16

You have to use an auxiliary table to fill in the values that are missing from the database now.

E 12°55'05.25"
N 56°04'39.16"

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-07-23 : 06:06:02

Take a look here: http://www.sqlservercentral.com/articles/TSQL/62867/ and scroll down to "Dozens of Other Uses".

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-23 : 06:28:28

or even create a number table yourselves with the help of CTE and left join the rest of query to it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources