Consultas de actividad de proyecto
Puede utilizar las consultas de este artículo para crear visualizaciones de datos similares a las de Enhanced Analytics.
IMPORTANT
Las consultas producirán resultados similares a los que se muestran en el análisis mejorado, pero es posible que no coincidan exactamente.
Requisitos previos
Antes de empezar, debe
-
Establezca una conexión con la herramienta Business Intelligence (BI):
Una vez establecida una conexión, puede utilizar las consultas de este artículo para extraer y visualizar datos.
Eventos de inicio de sesión de usuarios de Proyectos
Muestra la cantidad de personas asignadas al proyecto que iniciaron sesión en un día determinado.
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
tds.projectid,
ads.calendardate,
count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid and TO_DATE(ul.lastlogindate) = ads.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventos de inicio de sesión de usuarios de Proyectos: explorar en profundidad
WITH userlogins as (
SELECT
userid,
lastlogindate
FROM (
SELECT
userid,
lastlogindate,
lag(lastlogindate, 1, '1990-01-01') OVER (PARTITION BY userid ORDER BY begin_effective_timestamp) as previous_login
FROM users_event
)
WHERE lastlogindate != previous_login
)
SELECT
tds.projectid,
ul.userid,
ads.calendardate,
count(1)
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN userlogins ul ON ads.assignedtoid = ul.userid AND TO_DATE(ul.lastlogindate) = ads.calendardate
group by tds.projectid, ul.userid, ads.calendardate
Eventos de cambio de estado de tareas de usuarios de proyectos
Muestra la cantidad de personas que cambiaron el estado de una tarea para el proyecto en un día determinado.
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
count(tds.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventos de cambio de estado de tareas de usuarios de proyectos: explorar en profundidad
WITH task_status_changes as (
SELECT
taskid,
status,
lastupdatedbyid,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lastupdatedbyid,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status != 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
tsc.lastupdatedbyid,
count(tsc.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate
Eventos de finalización de tareas de usuarios de proyectos
Mostrar el número de personas que completaron una tarea del proyecto en un día determinado.
WITH task_status_changes as (
SELECT
taskid,
status,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status = 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
count(tds.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid and tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid and tsc.taskid = tds.taskid and TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, ads.calendardate
Eventos de finalización de tareas de usuarios de Projects: explorar en profundidad
WITH task_status_changes as (
SELECT
taskid,
status,
lastupdatedbyid,
begin_effective_timestamp
FROM (
SELECT
taskid,
status,
begin_effective_timestamp,
lastupdatedbyid,
lag(status, 1, 'NOSTATUS') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_status
FROM tasks_event
WHERE status = 'CPL'
)
WHERE status != previous_status
)
SELECT
tds.projectid,
tsc.lastupdatedbyid,
count(tsc.status),
ads.calendardate
FROM assignments_daily_history ads
INNER JOIN tasks_daily_history tds ON ads.taskid = tds.taskid AND tds.calendardate = ads.calendardate
INNER JOIN task_status_changes tsc ON tsc.taskid = ads.taskid AND tsc.taskid = tds.taskid AND TO_DATE(tsc.begin_effective_timestamp) = tds.calendardate
GROUP BY tds.projectid, tsc.lastupdatedbyid, ads.calendardate
recommendation-more-help
5f00cc6b-2202-40d6-bcd0-3ee0c2316b43