History of logged users and Maintenance Orders

This report provides a daily overview of logged-in users and maintenance orders. It includes the following data:

Week: The ISO week number for each date.
Date: The specific date for which data is being displayed.
Logged Users: The number of unique users who logged in on that day.
MO Type: The type of maintenance orders created on that date.
Count of New Maintenance Orders: The number of new maintenance orders created on the given date.
Count of Finished Maintenance Orders: The number of maintenance orders closed on the given date.

The report helps monitor user activity and maintenance operations on a daily and weekly basis.

SQL

WITH logged_in_users AS (
    SELECT 
        ua.id_user, 
        ua.time_created AS login_time,
        LEAD(ua.time_created, 1, NULL) OVER (PARTITION BY ua.id_user ORDER BY ua.time_created) AS next_event_time,
        ua.action
    FROM 
        user_auth_history ua
    WHERE 
        ua.action IN ('endpoint-loginAction', 'endpoint-logoutAction', 'system-logoutAction', 'system-deleteExpiredTokens')
),
min_login_time AS (
    SELECT CAST(MIN(login_time) AS DATE) AS min_date
    FROM logged_in_users
),
active_users_per_day AS (
    SELECT 
        CONVERT(VARCHAR, d, 23) AS report_date,
        COUNT(DISTINCT l.id_user) AS logged_users
    FROM 
        (SELECT DATEADD(DAY, v.number, min_date) AS d
         FROM min_login_time
         JOIN master.dbo.spt_values v ON v.type = 'P'
         WHERE v.number <= DATEDIFF(DAY, min_date, GETDATE())) AS all_dates
    LEFT JOIN 
        logged_in_users l 
        ON CAST(l.login_time AS DATE) <= all_dates.d
        AND (CAST(l.next_event_time AS DATE) > all_dates.d OR l.next_event_time IS NULL OR l.action = 'endpoint-loginAction')
    GROUP BY 
        CONVERT(VARCHAR, d, 23)
)
SELECT 
    'W' + CONVERT(VARCHAR, DATEPART(ISO_WEEK, CAST(a.report_date AS DATE))) AS [Week],
    a.report_date AS [Date],
    a.logged_users AS [Logged users],
    lv.title AS [MO Type],
    COUNT(CASE WHEN mo.time_created IS NOT NULL THEN 1 END) AS [Count of new MO],
    COUNT(CASE WHEN mo.time_finish IS NOT NULL THEN 1 END) AS [Count of finished MO]
FROM 
    active_users_per_day a
LEFT JOIN 
    maintenance_order mo ON CAST(mo.time_created AS DATE) = CAST(a.report_date AS DATE)
LEFT JOIN 
    listing_value lv ON mo.id_listing_value = lv.id
GROUP BY 
    a.report_date,
    DATEPART(ISO_WEEK, CAST(a.report_date AS DATE)),
    lv.title,
    a.logged_users
ORDER BY 
    a.report_date DESC

MSSQL

SQL

WITH logged_in_users AS (
    SELECT 
        ua.id_user, 
        ua.time_created AS login_time,
        LEAD(ua.time_created, 1, GETDATE()) OVER (PARTITION BY ua.id_user ORDER BY ua.time_created) AS next_event_time,
        ua.action
    FROM 
        user_auth_history ua
    WHERE 
        ua.action IN ('endpoint-loginAction', 'endpoint-logoutAction', 'system-logoutAction', 'system-deleteExpiredTokens')
),
min_login_time AS (
    SELECT CAST(MIN(login_time) AS DATE) AS min_date
    FROM logged_in_users
),
active_users_per_day AS (
    SELECT 
        FORMAT(d, 'yyyy-MM-dd') AS report_date,
        COUNT(DISTINCT l.id_user) AS logged_users
    FROM 
        (SELECT DATEADD(DAY, v.number, min_date) AS d
         FROM min_login_time
         JOIN master.dbo.spt_values v ON v.type = 'P'
         WHERE v.number <= DATEDIFF(DAY, min_date, GETDATE())) AS all_dates
    LEFT JOIN 
        logged_in_users l 
        ON CAST(l.login_time AS DATE) <= all_dates.d
        AND (CAST(l.next_event_time AS DATE) > all_dates.d OR l.action = 'endpoint-loginAction')
    GROUP BY 
        FORMAT(d, 'yyyy-MM-dd')
)
SELECT 
    'W' + FORMAT(CAST(a.report_date AS DATE), 'IW') AS [Week],
    a.report_date AS [Date],
    a.logged_users AS [Logged users],
    lv.title AS [MO Type],
    COUNT(CASE WHEN mo.time_created IS NOT NULL THEN 1 END) AS [Count of new MO],
    COUNT(CASE WHEN mo.time_finish IS NOT NULL THEN 1 END) AS [Count of finished MO]
FROM 
    active_users_per_day a
LEFT JOIN 
    maintenance_order mo ON CAST(mo.time_created AS DATE) = CAST(a.report_date AS DATE)
LEFT JOIN 
    listing_value lv ON mo.id_listing_value = lv.id
GROUP BY 
    a.report_date,
    FORMAT(CAST(a.report_date AS DATE), 'IW'),
    lv.title,
    a.logged_users
ORDER BY 
    a.report_date DESC

Oracle:

SQL

WITH logged_in_users AS (
    SELECT 
        ua.id_user, 
        ua.time_created AS login_time,
        LEAD(ua.time_created, 1, SYSDATE) OVER (PARTITION BY ua.id_user ORDER BY ua.time_created) AS next_event_time,
        ua.action
    FROM 
        user_auth_history ua
    WHERE 
        ua.action IN ('endpoint-loginAction', 'endpoint-logoutAction', 'system-logoutAction', 'system-deleteExpiredTokens')
),
min_login_time AS (
    SELECT TRUNC(MIN(login_time)) AS min_date
    FROM logged_in_users
),
active_users_per_day AS (
    SELECT 
        TO_CHAR(d, 'YYYY-MM-DD') AS report_date,
        COUNT(DISTINCT l.id_user) AS logged_users
    FROM 
        (SELECT min_date + LEVEL - 1 AS d
         FROM min_login_time
         CONNECT BY LEVEL <= TRUNC(SYSDATE) - min_date + 1) all_dates
    LEFT JOIN 
        logged_in_users l 
        ON TRUNC(l.login_time) <= all_dates.d
        AND (TRUNC(l.next_event_time) > all_dates.d OR l.action IN ('endpoint-loginAction'))
    GROUP BY 
        TO_CHAR(d, 'YYYY-MM-DD')
)
SELECT 
    'W' || TO_CHAR(TO_DATE(a.report_date, 'YYYY-MM-DD'), 'IW') AS "Week",
    a.report_date AS "Date",
    a.logged_users AS "Logged users",
    lv.title AS "MO Type",
    COUNT(CASE WHEN mo.time_created IS NOT NULL THEN 1 END) AS "Count of new MO",
    COUNT(CASE WHEN mo.time_finish IS NOT NULL THEN 1 END) AS "Count of finished MO"
FROM 
    active_users_per_day a
LEFT JOIN 
    maintenance_order mo ON TRUNC(mo.time_created) = TO_DATE(a.report_date, 'YYYY-MM-DD')
LEFT JOIN 
    listing_value lv ON mo.id_listing_value = lv.id
GROUP BY 
    a.report_date,
    TO_CHAR(TO_DATE(a.report_date, 'YYYY-MM-DD'), 'IW'),
    lv.title,
    a.logged_users
ORDER BY 
    a.report_date DESC