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 |
|
BigBolts
Starting Member
5 Posts |
 Posted - 2011-11-29 : 12:13:04
|
| I have a table which records logins. Each Login has an id, session id, type and datetime. I need a query that will count up the number of concurrent logins in a day and return the maximum reached. Example:ID, SessionID, Type, Datetime counter = 0 1, 1, Logged On, 2011-11-29 10:15:23.000 counter = 12, 2, Logged On, 2011-11-29 10:23:28.000 counter = 23, 1, Logged Off, 2011-11-29 11:33:18.000 counter = 24, 3, Logged On, 2011-11-29 12:47:02.000 counter = 25, 3, Logged Off, 2011-11-29 12:57:19.000 counter = 26, 2, Logged Off, 2011-11-29 13:01:58.000 counter = 2This would result in a return value of 2 for the 29th. My idea is that for each day and each logon a counter increments by 1. While the total number of concurrent logins remains less than the counter, it does not increase. I've tried various "select case when count", but I can't get it to work. Any suggestions would be great! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-11-29 : 12:19:15
|
| can you explain how you got 2 for last record also? it should be 1 right?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
BigBolts
Starting Member
5 Posts |
Posted - 2011-11-29 : 12:30:44
|
| The counter does not decrease, only increase. The Logon sessionid 3 will not increase the counter because the total concurrent logins remains at 2. Does that explain? |
|
|
|
|
|
|
|
|
count maximum occurences in a given period