User Active Intervals View
- Devan Robertson
Name | UserActiveIntervals |
|---|---|
Type | View |
Description | A count of actions completed by users in 30 minute intervals. Only intervals with at least one completed action are included and these are considered active intervals. |
Data Category | Event Field/Column Name | Data Type | Description | Applicability | Sample Value | Notes |
|---|---|---|---|---|---|---|
Key | TenantId | VARCHAR | ALWAYS | qvo-stg |
| |
Key | Tenant | VARCHAR |
| ALWAYS | Default |
|
Key | ClientId | VARCHAR | ALWAYS | Default |
| |
Key | Client | VARCHAR |
| ALWAYS | Default |
|
Activity Details | PerformedByUserName | VARCHAR | Username of the user that completed the action. | ALWAYS | devan.robertson@quavo.com |
|
Activity Details | PerformedBy | VARCHAR | First initial and last name of the user that completed the action. | ALWAYS | D. Robertson |
|
Activity Details | Interval | DATE | Datetime that represents the start time of a 30 minute activity interval. | ALWAYS | 2024-11-23 00:00:00.000 |
|
Activity Details | Actions | Number | Count of actions completed during interval. | ALWAYS | 3 |
|
SQL
For greater transparency, and to provide clients with a “jumping off” point for their own queries, the SQL used to generated this view is provided below.
SELECT
a.tenantid,
t.name,
a.clientid,
c.commonname,
addedbyid,
CONCAT(LEFT(u.firstname, 1), '. ', u.lastname) AS PerformedBy,
IFF(MINUTE(a.createdatetime) < 30,
TO_TIMESTAMP_TZ(DATEADD(MI, 0, DATEADD(H, HOUR(a.createdatetime), TO_DATE(a.createdatetime)))::varchar || ' +0000'),
TO_TIMESTAMP_TZ(DATEADD(MI, 30, DATEADD(H, HOUR(a.createdatetime), TO_DATE(a.createdatetime)))::varchar || ' +0000')) AS "Interval",
count(*) AS "Actions"
FROM completedactions AS a
JOIN userprofile AS u ON a.tenantid = u.tenantid AND a.addedbyid = u.userid
JOIN tenant AS t ON a.tenantid = t.tenantid
JOIN client AS c ON a.tenantid = c.tenantid AND a.clientid = c.clientid
WHERE
addedbyid != 'System'
GROUP BY a.tenantid, t.name, a.clientid, c.commonname, addedbyid, PerformedBy, "Interval"
ORDER BY t.name, c.commonname, PerformedBy, "Interval" DESC
Change Log
Date | Change Summary |
|---|---|
12/11/2024 |
|