Perhaps 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:

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 )

);

And for the single statement solution:

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

Polite comments are welcome