Calculate concurrent user sessions on Access DB

kalics
kalics used Ask the Experts™
on
Hi, I have an Access 2016 database which has a table with user sessions (session ID's, application name, session start date/time and session end date/time). I need to find out the maximum concurrency for each application for each date.

I currently have a query as below which is taking too long (20 mins) to run but also comes out with only one day. Please can someone help with the algorithm for concurrency calculation  for Access?

SELECT TOP 1 Count(*) AS ['concurrency'], T1.Date
FROM table AS T1, table AS T2
WHERE (((T1.startTime_f) Between [T2].[startTime_f] And [T2].[endTIme_f]))
GROUP BY T1.usersessionID, T1.Date, t2.date;
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Would suggest an index on the startTime_f field.
If that doesn't help, perhaps you can move over this table to SQL Server Express (free edition).
Then Access is capable of executing Pass-Through queries really fast.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
First, create a query that lists the timeslots used:

SELECT 
    UserLog.StartTime, 
    UserLog.EndTime
FROM 
    UserLog
GROUP BY 
    UserLog.StartTime, 
    UserLog.EndTime;

Open in new window

Save this as TimeSlots.

Then find the count of users having used any of these time slots:

SELECT 
    TimeSlots.StartTime, 
    Count(*) AS Users
FROM 
    UserLog, 
    TimeSlots
WHERE 
    (TimeSlots.StartTime Between [UserLog]![StartTime] And [UserLog]![EndTime]) 
    OR 
    (TimeSlots.EndTime Between [UserLog]![StartTime] And [UserLog]![EndTime])
GROUP BY 
    TimeSlots.StartTime;

Open in new window

Save this as TimeSlotUsers.

Finally, find the maximum count of concurrent users:

SELECT 
    DateValue([StartTime]) AS [Date], 
    Max(TimeSlotUsers.Users) AS MaxConcurrentUsers
FROM 
    TimeSlotUsers
GROUP BY DateValue([StartTime]);

Open in new window

Of course, indexes on the time fields is a must.

Author

Commented:
Thanks very much Gustav. This works perfectly. Only one little question though - I have user session data for multiple applications on the same table. These queries work perfectly for concurrency for one application.

When I did try to customise this for multiple applications, it comes up with the output of different applications listed, but it doesn't consider each application as unique & does not calculate concurrency for that application, but considers only the time fields and therefore provides the concurrency for the overall database.

Can you please help with any thoughts as to how to list max concurrency for each application?

Much appreciated
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
That could be (air code):

SELECT 
    ApplicationId,
    UserLog.StartTime, 
    UserLog.EndTime
FROM 
    UserLog
GROUP BY 
    ApplicationId,
    UserLog.StartTime, 
    UserLog.EndTime;


SELECT 
    ApplicationId,
    TimeSlots.StartTime, 
    Count(*) AS Users
FROM 
    UserLog, 
    TimeSlots
WHERE 
    (UserLog.ApplicationId = TimeSlots.ApplicationId)
    AND
    ((TimeSlots.StartTime Between [UserLog]![StartTime] And [UserLog]![EndTime]) 
    OR 
    (TimeSlots.EndTime Between [UserLog]![StartTime] And [UserLog]![EndTime]))
GROUP BY 
    ApplicationId,
    TimeSlots.StartTime;


SELECT 
    ApplicationId,
    DateValue([StartTime]) AS [Date], 
    Max(TimeSlotUsers.Users) AS MaxConcurrentUsers
FROM 
    TimeSlotUsers
GROUP BY 
    ApplicationId,
    DateValue([StartTime]);

Open in new window

Author

Commented:
Thanks very much Gustav. It worked like magic. Can't thank you enough.

Author

Commented:
Thank you so much. Incredibly helpful.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial