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.
| Author |
Topic |
|
Stanley Tan
Starting Member
25 Posts |
 Posted - 2002-06-10 : 08:00:55
|
| I have a table called SignIns which records every time a user signs in to the site. I want to get the number of days the person signed in per month, so I use the following query:SELECT COUNT(DISTINCT DAY(TimeSignedIn)) FROM SignIns WHERE MONTH(TimeSignedIn) = MONTH(GETDATE()) AND YEAR(TimeSignedIn) = YEAR(GETDATE()) AND Username = '...'But I am having a hard time thinking of a query to select the number of days a certain user has signed-in overall. Can someone help me please? |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2002-06-10 : 08:09:06
|
| Just:-SELECT COUNT(DISTINCT DAY(TimeSignedIn)) FROM SignIns WHERE Username = '...' Would give you the amount of days user had signed in ever...Or am I missing the point of what you want here???PeaceRick |
 |
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-06-10 : 11:37:02
|
| That would return a maximum of 31 right? I want to be able to select the number of days a certain user has signed-in overall over a period that extends beyond a month, a year for example. |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-10 : 11:53:08
|
| SELECT COUNT(DISTINCT DAY(TimeSignedIn)) FROM SignIns WHERE timesigned between 'fromdate' to 'todate'Username = '...' -------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-06-10 : 12:00:35
|
| The day function is only going to give you 31 distinct values. You'll need to use the convert function to get the total number of days when spaning more than a month. Is this more like what you are looking for?SELECT COUNT(DISTINCT convert(varchar(10), TimeSignedIn, 101)) FROM SignIns WHERE timesignedin between 'fromdate' to 'todate' Username = '...'Jeff BanschbachConsultant, MCDBAEdited by - efelito on 06/10/2002 12:03:04 |
|
|
|
Stanley Tan
Starting Member
25 Posts |
Posted - 2002-06-10 : 23:05:57
|
| Thanks. The convert function is exactly what I needed! |
|
|
|
|
|
|
COUNT Query