SQL Kiwi (2/9/2013)
Kwisatz78 (2/8/2013)
I 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.This was the subject of a series of articles by Itzik Ben-Gan. The fastest solution found was submitted by, among others, our very own R Barry Young. You can read all about it here:
http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407
Be sure to read the whole thing, not just the first page. I have a SQLCLR solution that beats that by around 30% but unless you really need that extra bit of speed (and are quite expert with T-SQL and SQLCLR) I would stick with Barry's code.
Freakin' awesome link, Paul. I was able to modify Barry's code to also correctly populate the MX column for the Logoffs so that I could graph the "valleys" as well as the "peaks". I've been trying to do this solution in a similar fashion and got seriously hooked because I just didn't see the 2:1 ratio that Barry included in his final formula. Thanks for posting the link. It's definitely a keeper.
Barry, if you read this post, I know it's been 3 years since you wrote the code and that article came out but thanks a million to you for writing the code and to Itzik for 'splainin' it.
--Jeff Moden
Change is inevitable... Change for the better is not.