InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 18 May 2013 10:13:24 GMT20http://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxPerhaps a bit late but a different solution in one statement :-)To set the data up based on the solution of http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407:[code="sql"]USE tempdb; IF OBJECT_ID('dbo.Sessions', 'U') IS NOT NULL DROP TABLE dbo.Sessions;CREATE TABLE dbo.Sessions ( keycol INT NOT NULL, app VARCHAR(10) NOT NULL, usr VARCHAR(10) NOT NULL, host VARCHAR(10) NOT NULL, starttime DATETIME NOT NULL, endtime DATETIME NOT NULL, CONSTRAINT PK_Sessions PRIMARY KEY(keycol), CHECK(endtime > starttime) ); GO CREATE INDEX idx_nc_app_st ON dbo.Sessions(app, starttime) ;CREATE INDEX idx_nc_app_et ON dbo.Sessions(app, endtime);GO--- Populate the table declare @i int = 1 declare @DT_Rnd datetime while @i < 1000 begin Set @i = @i + 1 set @DT_Rnd = dateadd( mi , RAND()* 1440 , cast('20090212' as datetime) ) INSERT tempdb.dbo.Sessions(keycol, app, usr, host, starttime, endtime) VALUES( @i , 'app' + right( '00' + CAST ( 1 + cast( RAND()* 15 as int) as varchar(2)) , 2) , 'user' + right('000' + CAST ( 1 + cast( RAND()* 150 as int) as varchar(2)) , 3) , 'host' + right('000' + CAST ( 1 + cast( RAND()* 240 as int) as varchar(2)) , 3) , @DT_Rnd , dateadd( mi , 5 + (RAND()* 50) , @DT_Rnd ) ); [/code]And for the single statement solution:[code="sql"] Select APP , [No of Concurrent users] , [Point in Time] from ( Select Toe.app , Toe.[Point in Time] , [No of Concurrent users] = COUNT(distinct keycol) , RID = row_number() over ( partition by toe.App order by COUNT(distinct keycol) desc ) from ( -- Time of Events select app , [Point in Time] = starttime from tempdb.dbo.Sessions union Select app , [Point in Time] = endtime from tempdb.dbo.Sessions ) as TOE inner join tempdb.dbo.Sessions as S1 on s1.App = Toe.app and Toe.[Point in Time] >= s1.starttime and Toe.[Point in Time] < s1.Endtime group by Toe.app , Toe.[Point in Time] ) as c where RID = 1 order by App , RID [/code]Polite comments are welcomeFri, 15 Feb 2013 11:20:56 GMTSteve JPhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxI just wanted to let you all know that I implemented the solution and it works a treat as the previous poster says many thanks to Barry and Itzik.Fri, 15 Feb 2013 03:50:30 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]Jeff Moden (2/9/2013)[/b][hr][quote][b]SQL Kiwi (2/9/2013)[/b][hr][quote][b]Kwisatz78 (2/8/2013)[/b][hr]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.[/quote]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:[url]http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407[/url]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.[/quote]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.[/quote]Truly an amazing solution for this problem. +10 to Barry and Itzik!Sun, 10 Feb 2013 18:44:46 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxCrickey guys thanks very much for all the replies, I will delve properly into them tomorrow when back at work, I decided to give myself a weekend off this week and took some R&R, but will definitely post back if I get stuck further.Thanks againSun, 10 Feb 2013 14:02:03 GMTKwisatz78http://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx@Kwisatz78,Are you all set now or do you need some additional help?Sun, 10 Feb 2013 13:56:57 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxI've worked with the "difference between ROW_NUMs" a lot and, like you say, once you've got the concept down, it's very simple. Heh... unless you did like I originaly did and miss the bloody 2:1 ratio that Barry used in his final calculation.Thanks again, Paul.Sun, 10 Feb 2013 00:07:43 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]Jeff Moden (2/9/2013)[/b][hr]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.[/quote]Yes, it's very clever but quite simple at the same time, once the concepts sink in. Once SQL Server supports proper ordered aggregates, the problem will be trivial.Sat, 09 Feb 2013 22:35:51 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]SQL Kiwi (2/9/2013)[/b][hr][quote][b]Kwisatz78 (2/8/2013)[/b][hr]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.[/quote]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:[url]http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407[/url]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.[/quote]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.Sat, 09 Feb 2013 22:11:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]Kwisatz78 (2/8/2013)[/b][hr]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.[/quote]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:[url]http://www.sqlmag.com/article/tsql3/calculating-concurrent-sessions-part-3-103407[/url]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.Sat, 09 Feb 2013 18:13:31 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]drew.allen (2/8/2013)[/b][hr]The solution provided in your link is likely to be the fastest. (It essentially the same as what Paul is suggesting, but limiting the buckets to only the specific login times.) If you post what you have already tried and where you ran into problems, we can help you understand how it works.[/quote]If you're talking about Alex K's solution, it's absolutely horrible. If you take a look at the Actual Execution Plan, it has a full blown accidental CROSS JOIN in it for smaller numbers of rows and a full blown Triangular Join in it for larger numbers. I wouldn't use that code if it was the only way to get this problem done.Sat, 09 Feb 2013 15:33:13 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxThe solution provided in your link is likely to be the fastest. (It essentially the same as what Paul is suggesting, but limiting the buckets to only the specific login times.) If you post what you have already tried and where you ran into problems, we can help you understand how it works.Fri, 08 Feb 2013 12:14:31 GMTdrew.allenhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspx[quote][b]Kwisatz78 (2/8/2013)[/b][hr]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-recordsHowever 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 gratefulThanks.[/quote]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.Fri, 08 Feb 2013 09:59:13 GMTPaulB-TheOneAndOnlyhttp://www.sqlservercentral.com/Forums/Topic1417803-392-1.aspxHi 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-recordsHowever 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 gratefulThanks.Fri, 08 Feb 2013 09:53:10 GMTKwisatz78