Date order by problem - 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)

Date order by problem

Author

Topic

kevin.woodhouse
Starting Member

1 Post

Posted - 2009-03-24 : 12:19:56

OK, here goes.

I have a cisco Access Control Server (ACS) and send failed authentications successfully to a SQL 2005 database. The table is called failedattempts. I want to be able to select the username and the total number of times that user has failed authentication. I can get to a point where I can pick a unique username and display the number of times that user has failed authentication with the following SQL:

SELECT user_Name, COUNT(user_Name)
FROM failedattempts GROUP BY user_Name
ORDER BY count( user_Name) DESC

This results in the following table:

User1 12
User2 10
User3 2

No problem so far, however I want to be able to sort on todays date. For example, show me all the failed attempts today, the above list displays all failed attempts since the database was created.

If I try to manipulate the results with any date information SQL provides me with a very polite error, there is the problem:

Any ideas to sort on date please.
Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 13:35:18

If you want just the failed attempts today..include a WHERE logic in your select statement as below...

SELECT user_Name, COUNT(user_Name)
FROM failedattempts WHERE date = today's date GROUP BY user_Name
ORDER BY count( user_Name) DESC

If you need all the attempts grouped by fdate...use the date column as well in the GROUP BY clause.

SELECT user_Name, COUNT(user_Name),date
FROM failedattempts GROUP BY user_Name,date
ORDER BY count( user_Name) DESC

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-25 : 06:18:44

SELECT user_Name, COUNT(user_Name)
FROM failedattempts
WHERE date_col>=dateadd(day,datediff(day,0,getdate()),0) and date_col<dateadd(day,datediff(day,0,getdate()),1)
GROUP BY user_Name
ORDER BY count( user_Name) DESC

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources