Consultas de atividade do projeto
Última atualização: 26 de abril de 2025
Criado para:
- Usuário
Você pode usar as queries neste artigo para criar visualizações de dados semelhantes às do Enhanced Analytics.
As consultas produzirão resultados semelhantes aos mostrados no Enhanced Analytics, mas podem não corresponder exatamente.
Pré-requisitos
Antes de começar, você deve
-
Estabeleça uma conexão com a ferramenta Business Intelligence (BI):
Depois de estabelecer uma conexão, você pode usar as consultas neste artigo para extrair e visualizar dados.
Eventos de logon de usuários de projetos
Mostra o número de pessoas atribuídas ao projeto que fizeram logon em um determinado dia.
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 logon de usuários de projetos: detalhar
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 alteração de status de tarefa de usuários de projetos
Mostra o número de pessoas que alteraram o status de uma tarefa para o projeto em um determinado dia.
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 alteração de status de tarefa de usuários de projetos: detalhar
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 conclusão de tarefas de usuários de projetos
Mostrar o número de pessoas que concluíram uma tarefa para o projeto em um determinado dia.
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 conclusão de tarefas de usuários de projetos: detalhar
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