Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL query to provide total count of 'users' logged in today for each known 'location'

Posted on 2006-04-14
7
Medium Priority
?
580 Views
Last Modified: 2008-01-09
I've got what I suspect will turn out to be a pretty simple query, but I've been playing around in SQL2K for longer that I want to and been unable to acheive the result I want..

Basically, I've got a table ('Logins') with 3 columns:  'User', 'LastLogon' and 'Location' and currently use the following SQL statement to get a count of the number of user logins per location so far today:  "SELECT Location,COUNT(Location) FROM Logins where DATEDIFF(day,LastLogon,GETDATE())=0 GROUP BY Location ORDER BY Location"

This correctly returns those Locations where a user has actually logged in today and is fine in itself.

However, along with those locations logged in to today, I also want to list those other known locations without any logins today. For instance, if Locations A and B each have 10 logins today and Location C has 0, I want the results set to be:

A, 10
B, 10
C, 0

TIA..
0
Comment
Question by:fostejo
  • 3
  • 2
  • 2
7 Comments
 
LVL 9

Expert Comment

by:raopsn
ID: 16454822
This should give you that:

SELECT Location,
COUNT(Case when DATEDIFF(day,LastLogon,GETDATE())=0  Then 1 Else 0 End )
FROM Logins
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 16454830
Try this:

select L.Location, IsNull(Count(C.location) ,0)
From (select location from logins group by location) L
Left Join (Select Location,COUNT(Location) FROM Logins where DATEDIFF(day,LastLogon,GETDATE())=0 GROUP BY Location) C on L.Location = C.Location
Order By L.Location
0
 
LVL 10

Author Comment

by:fostejo
ID: 16455076
raopsn & MikeToole..

raopsn - While the specified query does return all the expected Locations, the count of logins for today is wrong (they should all be zero at the moment, but the query appears to be returning all records for that location, not just those logged in today)

MikeToole - Your query errors with a 'No column was specified for column 2 of 'C''

Thanks so far guys..
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 27

Accepted Solution

by:
MikeToole earned 500 total points
ID: 16455143
Oops, that's what comes from winging it!

select L.Location, IsNull(C.LogCount) ,0)
From (select location from logins group by location) L
Left Join (Select Location, COUNT(Location) as LogCount FROM Logins where DATEDIFF(day,LastLogon,GETDATE())=0 GROUP BY Location) C on L.Location = C.Location
Order By L.Location
0
 
LVL 10

Author Comment

by:fostejo
ID: 16455237
Thanks MikeToole,

After getting rid of the erroneous bracket in the IsNull statement that works perfectly...  ;)

raopsn - I like the simplicity of your query and don't understand why it isn't working as it looks logically correct! ... thanks anyway.
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 16455324
I like the simplicity of raopsn's query too.
The fix for it is to use Sum() rather than Count() to aggregate the values.
0
 
LVL 9

Expert Comment

by:raopsn
ID: 16455633
That could be because of time part you have in the date fields
Try this...

SELECT Location,
COUNT(
   Case when Convert(Char, LastLogon, 101) = Convert(Char, GETDATE(), 101) = 0  Then 1 Else 0 End )
FROM Logins
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question