How to find maximum concurrent users and sessions by Login and Logout Time?

Transact-SQL
1

I am trying to figure out the solution using co-related sub-query but somehow getting stuck with 'odd-dates' instead of # of max(concurrent users) and sessions for particular applications. Any help would be really appreciable.

Below is the reference query and output snapshot:-

SELECT ExtensionID, ClientCode, LoginTime, LogoutTime
FROM ( SELECT ExtensionID, ClientCode, AuditDate as LoginTime, Change, isnull(LEAD(AuditDate) OVER (PARTITION BY ExtensionID ORDER BY AuditDate), GETDATE()) as LogoutTime
FROM (SELECT ExtensionID, ClientCode, AuditDate, case when AssignedEmployeeID is not null then 1 when AssignedEmployeeID is null then -1 else 0 end as change
from Focus.Mallaka.TollExtensions as ext with (nolock)
WHERE AuditDate between DATEADD(Day,-7,GETDATE()) and GETDATE()
) as subQ
) as LeadSubQuery
WHERE Change = 1
order by ExtensionID, LoginTime;

image

2

Please do NOT use with(nolock) if you care about getting correct results! See discussion in other threads - just search for "nolock".

Don't know if I understand exactly what you want, but try this:

select a.extensionid
      ,a.clientcode
      ,a.logintime
      ,a.logouttime
  from focus.mallaka.tollextensions as a
       inner join focus.mallaka.tollextensions as b
               on b.extensionid=a.extensionid
              and b.clientcode=a.clientcode
              and b.logintime<=a.logouttime
              and b.logouttime>=a.logintime
              and b.logintime!=a.logintime
              and b.logouttime!=a.logouttime
;
3

Hi bitsmed, Thank you for your response.

Objective of the problem statement: I do want to track the utilization of application associated as 'ClientCode' in the query.

  • Maximum number of concurrent users by given Login & Logout Time.

Regards.

4

Does the query I showed, display the rows that qualify your rules of "concurrent users"?

5

It's appreciable that you took time for this.

Actually, the LogonTime and Logout Time columns are temporary derived columns as result-set.

I do want to append reference code into the main query. Please, see if this makes a sense to solve it. Below are the details:-

Reference Code:

select system, cast(logintime as date) as thedate,
max(coalesce(cumeins, 0) - coalesce(cumeouts, 0)) as concurrent
from (select l.logintime,
(select count()
from logins l2
where l2.system = l.system and l2.logintime <= l.logintime
) as cumeins,
(select count()
from logins l2
where l2.system = l.system and l2.logouttime <= l.logintime
) as cumeouts
from logins l
) l
group by system, cast(logintime as date);

Main Query:-

SELECT ExtensionID, ClientCode, LoginTime, LogoutTime
FROM ( SELECT ExtensionID, ClientCode, AuditDate as LoginTime, Change, isnull(LEAD(AuditDate) OVER (PARTITION BY ExtensionID ORDER BY AuditDate), GETDATE()) as LogoutTime
FROM (SELECT ExtensionID, ClientCode, AuditDate, case when AssignedEmployeeID is not null then 1 when AssignedEmployeeID is null then -1 else 0 end as change
from Focus.Mallaka.TollExtensions as ext with (nolock)
WHERE AuditDate between DATEADD(Day,-7,GETDATE()) and GETDATE()
) as subQ
) as LeadSubQuery
WHERE Change = 1
order by ExtensionID, LoginTime;

Regards,
Deepak

6

hi deepak

I know this topic is long time ago

i was looking at it now

I tried to solve it

Please take a look at it and let me know
:slight_smile:
:slight_smile:

I have not put any NULLS in data
If this looks ok then I can try putting NULLS

drop create data 
use tempdb 
go 


drop table logins 
go

create table logins 
(
logintime datetime null,
logouttime datetime null,
system int null
)
go

insert into logins select '2018-07-09 10:10:11.433','2018-07-09 10:30:11.433',12
insert into logins select '2018-07-09 10:10:11.433','2018-07-09 10:45:11.433',12
insert into logins select '2018-07-09 12:10:11.433','2018-07-09 13:10:11.433',12
insert into logins select '2018-07-09 09:10:11.433','2018-07-09 15:10:11.433',12
insert into logins select '2018-07-10 18:10:11.433','2018-07-10 20:10:11.433',12
insert into logins select '2018-07-10 11:10:11.433','2018-07-10 15:10:11.433',12
insert into logins select '2018-07-10 20:10:11.433','2018-07-10 23:10:11.433',12
insert into logins select '2018-07-11 10:10:11.433','2018-07-11 14:10:11.433',12
insert into logins select '2018-07-11 17:10:11.433','2018-07-11 21:10:11.433',12
go
SQL 
SELECT a.system, 
       a.logintime, 
       ( b.logincount - a.logoutcount ) AS maxconcurrentusers 
FROM   (SELECT a.system, 
               a.logintime, 
               Count(b.logouttime) AS logoutcount 
        FROM   (SELECT system, 
                       logintime 
                FROM   logins) a 
               JOIN (SELECT system, 
                            logouttime 
                     FROM   logins) b 
                 ON b.logouttime <= a.logintime 
                    AND a.system = b.system 
        GROUP  BY a.system, 
                  a.logintime) a 
       JOIN (SELECT a.system, 
                    a.logintime, 
                    Count(c.logintime) logincount 
             FROM   (SELECT system, 
                            logintime 
                     FROM   logins) a 
                    JOIN (SELECT system, 
                                 logintime 
                          FROM   logins) c 
                      ON c.logintime <= a.logintime 
                         AND a.system = c.system 
             GROUP  BY a.system, 
                       a.logintime) b 
         ON a.system = b.system 
            AND a.logintime = b.logintime
Results

image