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