SELECT COUNT of common user, but different date

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)

SELECT COUNT of common user, but different date

Author

Topic

greenstone90
Starting Member

8 Posts

Posted - 2010-02-27 : 10:22:11

Hi,

I have a website, and am logging every request (including unique username) to my SQL Server

database table.

The database table columns are:
ID, USERNAME, DATETIME, ...

I'm trying to write a SqlServer SELECT query that will tell me: How many users come on more than 1

day (repeat customers) to make requests?

Idea of how to make this SELECT statement?

Does this require a join? How do I strip the "time" part of the date to do the compare?

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 10:44:15

no need of join. just use


SELECT USERNAME
FROM table
GROUP BY USERNAME
HAVING COUNT(DISTINCT DATETIME)>1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-27 : 12:11:12

"How do I strip the "time" part of the date to do the compare?"

DATEADD(Day, DATEDIFF(Day, 0 MyDateValue), 0)

is the most efficient way to remove the Time from a DateTime datatype object.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 12:12:55

see this too

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

greenstone90
Starting Member

8 Posts

Posted - 2010-02-28 : 01:21:57

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-28 : 04:03:32

welcome