Kwisatz78 (2/8/2013)
Hi allI have a table which contains login and logout times for a large set of users, and we are wanting to know how to code it to pull back the maximum number of users who are logged on at any one time during that day.
I have got no where with this at present I have searched the internet and found something here:
http://stackoverflow.com/questions/1117004/find-number-of-concurrent-users-in-a-sql-records
However I can not figure out the solution mentioned and have been unable to get it to work. If anyone has any thoughts on how best to do this I would be grateful
Thanks.
One way would be to create a buckets table with one row representing each and every single minute of the day - is that granularity enough for you? - then, for each row on your login/logout table add 1 to all the buckets representing minutes the particular user was logged into the system.
At the end of the process just select the bucket with max() and the minute of the day represented by the winning bucket plus the value of the bucket would tell when the max() number of users was logged in and how many of them where logged in at the time.
Hope this helps.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.