count by days - 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 2000 Forums

Transact-SQL (2000)

count by days

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-11-15 : 02:05:21

Im having some problems with writing a SQL statement, wondering if anybody can lend a hand.

I have a table called tblUserDetails with a date column named LastLoggedIn. This column is updated to the current time when a user logs in.

What I need to do retrieve is - "SELECT count(userID) as Logins" that have logged in today, then I need to retreive how many users have logged in since yesterdays date, then how many users have logged in within the past 3 days, etc for 30 days.

Can anybody give me some help, im totally stuck..

thanks

mike123

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-15 : 04:14:10

quote:

Im having some problems with writing a SQL statement, wondering if anybody can lend a hand.

I have a table called tblUserDetails with a date column named LastLoggedIn. This column is updated to the current time when a user logs in.

What I need to do retrieve is - "SELECT count(userID) as Logins" that have logged in today, then I need to retreive how many users have logged in since yesterdays date, then how many users have logged in within the past 3 days, etc for 30 days.

Hmm. I wrote all this out but then had problems logging in. Anyway, if you only need a number for a set amount of days - and not a summary per day, you would need

SELECT count(userID) as Logins
where datediff (dd, LastLoggedIn, getdate()) <= @numberofdays

Where you set @numberofdays in your procedure as a parameter to hold the number of days you want the list for. (EG 0 would just get you people logged in in the last day.)

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-11-15 : 07:37:01

If you have and index on LastLoggedIn, that you'd like the optimizer to use, you'd be better off comparing LastLoggedIn to some variation of dateadd(), getdate, and the number of days....

Jay White
{0}

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-15 : 11:57:30

Create a table of 30 records, with a single Int being the key, from 1 to 30. Call the table "Days" and the int field "DayNumber".

Once again, we get to use a non equal join(my favorite!) :

SELECT DayNumber as [Last # of Days], COUNT(UserID) as TotalLogIns
FROM
LogTable
INNER JOIN
Days
ON
Days <= DateDiff(d,dateadd(d,-1 * DayNumber, getDate), LastLoggedIn)

I am writing this w/o a query analyzer on hand so it may need some tweaking, but you get the idea.

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2002-11-15 : 12:21:26

How about something like this???


SELECT  convert(varchar(10), lastloggedin, 101), count(userid)
FROM    tbluserdetails
WHERE   dateadd(d, 30, lastloggedin) > getdate()
GROUP BY convert(varchar(10), lastloggedin, 101)
ORDER BY convert(varchar(10), lastloggedin, 101)

Jeff Banschbach
Consultant, MCDBA

Edited by - efelito on 11/15/2002 12:21:52

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-11-18 : 03:16:14

worked like a charm, thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-02-16 : 05:07:12

This looks more like a design issue.

What you want is :

quote:
What I need to do retrieve is - "SELECT count(userID) as Logins" that have logged in today, then I need to retreive how many users have logged in since yesterdays date, then how many users have logged in within the past 3 days, etc for 30 days.

What you have is :

quote:
I have a table called tblUserDetails with a date column named LastLoggedIn. This column is updated to the current time when a user logs in.

Your current table design will only shows when the users is last log in. If for the past 30 days, a user has been login everyday, your table will only keep the last login time. You will need a separate table that keep track every login time to get what you want.

Are you sure you are getting what you want ? or maybe i misunderstood what you want.

----------------------------------
'KH'
Time is always against us

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources