"I'm working on a Delinea report and need to generate an SQL query to find the day with the highest number of active users simultaneously over the last 4 months. My database has columns like UserId, StartDate, and EndDate (which can be NULL if the session is still active).

The goal is to calculate the peak active users for a single day, considering a user as active if their session started before or on the evaluated day and has not ended (i.e., EndDate is NULL or in the future).

I'm having trouble with my query as it only returns results for today instead of the day with the maximum active users during the entire period.

How can I structure a query to calculate the day with the maximum number of active users over the last 4 months in a Delinea report?"

SELECT 
    CAST(StartDate AS DATE) AS Fecha,
    COUNT(DISTINCT UserId) AS UsuariosActivos
FROM tbsecretsession
WHERE 
    Active = 1
    AND StartDate >= DATEADD(MONTH, -4, GETDATE())  -- Últimos 4 meses
    AND (EndDate IS NULL OR EndDate >= GETDATE())  -- Considera sesiones no terminadas
GROUP BY 
    CAST(StartDate AS DATE)
ORDER BY 
    UsuariosActivos DESC  -- Ordena de mayor a menor

"I'm working on a Delinea report and need to generate an SQL query to find the day with the highest number of active users simultaneously over the last 4 months. My database has columns like UserId, StartDate, and EndDate (which can be NULL if the session is still active).

The goal is to calculate the peak active users for a single day, considering a user as active if their session started before or on the evaluated day and has not ended (i.e., EndDate is NULL or in the future).

I'm having trouble with my query as it only returns results for today instead of the day with the maximum active users during the entire period.

How can I structure a query to calculate the day with the maximum number of active users over the last 4 months in a Delinea report?"

SELECT 
    CAST(StartDate AS DATE) AS Fecha,
    COUNT(DISTINCT UserId) AS UsuariosActivos
FROM tbsecretsession
WHERE 
    Active = 1
    AND StartDate >= DATEADD(MONTH, -4, GETDATE())  -- Últimos 4 meses
    AND (EndDate IS NULL OR EndDate >= GETDATE())  -- Considera sesiones no terminadas
GROUP BY 
    CAST(StartDate AS DATE)
ORDER BY 
    UsuariosActivos DESC  -- Ordena de mayor a menor

• sql
• report

• Which dbms are you using? (The above query uses several product specific functions.) – jarlh Commented Mar 20 at 10:41
• 1 A minimal reproducible example is a great start when asking for SQL assistance, – jarlh Commented Mar 20 at 10:42
• Column StartDate data type? – jarlh Commented Mar 20 at 11:06

1 Answer 1

By selecting only the StartDate of your rows, you omit every "inside" day of multi-days sessions.

Enumerate all days

The simplest way of exploring every day's count of sessions is to

• generate a calendar of all days between 4 months ago and now
• then look up for users who were logged in at that time (by joining each day to sessions, with one join-spit row per day per session that began before end of the day, and finished after start of the day)
• then just count how many sessions each day has joined to

The calendar generating part will be handled by a recursive Common Table Expression, which starting with a single day (the first one of your 4 months span), incrementally adds days (by adding 1 day to the one produced by the previous iteration) until reaching today.

WITH alldays AS
(
    SELECT CAST(MIN(StartDate) AS DATE) AS Fecha FROM tbsecretsession WHERE StartDate >= DATEADD(MONTH, -4, GETDATE())  -- Últimos 4 meses
    UNION ALL
    SELECT DATEADD(day, 1, Fecha) FROM alldays WHERE Fecha < GETDATE()
)
SELECT 
    d.Fecha,
    COUNT(DISTINCT UserId) AS UsuariosActivos
FROM alldays d JOIN tbsecretsession s ON s.StartDate <= d.Fecha AND (s.EndDate IS NULL OR s.EndDate > d.Fecha)  -- Considera sesiones no terminadas
WHERE 
    Active = 1
GROUP BY 
    d.Fecha
ORDER BY 
    UsuariosActivos DESC  -- Ordena de mayor a menor

You can see this solution running on a small test set.

Compute count of overlapping sessions by comparing their start and end dates

A lighter but more brain-challenging solution exists, where instead of generating and filling one virtual cell per day,
you

• compute all the times where the count of users changes (either when a session starts, or a session ends),
• which gives you spans of stable count of users,
• then count the number of sessions compatible with each span (using the same technique as solution 1, except that instead of reporting on full days your report will include the precise times where user count changes).

Thus it will be useful if you want a report with aggregated spans of many days:

2 users from Jan 15 to Feb 1
3 users from Feb  2 to Feb 3

instead of:

Jan 15: 2 users
…
Jan 29: 2 users
Jan 30: 2 users
Jan 31: 2 users
Feb 1:  3 users
Feb 2:  3 users

Still you can generate that second type of report, using the generated calendar of solution 1, but you'll get more stress on your database (than with the pure second solution, but still less than with the pure first solution though)

WITH
    -- Work only with sessions on our observation range (from 4 months ago to now).
    s AS (SELECT StartDate, EndDate FROM tbsecretsession where EndDate IS NULL OR EndDate > DATEADD(MONTH, -4, GETDATE())),
    -- Enumerate all moments where the user count will evolve (either positively for a session start, or negatively for an end).
    changes AS (SELECT StartDate t, 1 UsersCountEvol FROM s UNION ALL SELECT EndDate, -1 FROM s),
    -- Sum over those moments, so that we keep only one count row if 2 users start a session at the same time (or one enters and one quits).
    delta AS (SELECT t, sum(UsersCountEvol) UsersCountEvol from changes GROUP BY t)
-- Now compute the current count of users as "sum of all deltas from start of time".
-- Thus if 1 user enters on day 1, 3 enter on day 2, and 2 leaves on day 3, we will have +1+3-2 = 2 users at that time.
SELECT t [From], lead(t) OVER timeline [Until], SUM(UsersCountEvol) OVER timeline UsariosActivos
FROM delta
WHERE t IS NOT NULL
WINDOW timeline AS (ORDER BY t)
--ORDER BY t
ORDER BY UsariosActivos DESC, t;

Which returns (here with ORDER BY t, to better understand the evolution of users count in time, but the query above ORDER BY UsariosActivos DESC to get the order you'd like):

(as seen in first part of the combined fiddle)