Dokumentation Workfront Workfront-Handbuch

Aktivität nach Team-Abfragen

Last update: Tue Mar 11 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

Themen:

Erstellt für:

Benutzende

Sie können die Abfragen in diesem Artikel verwenden, um Datenvisualisierungen zu erstellen, die denen in Enhanced Analytics ähneln.

IMPORTANT

Abfragen führen zu ähnlichen Ergebnissen wie die in Enhanced Analytics gezeigten, aber sie stimmen möglicherweise nicht genau überein.

Voraussetzungen

Bevor Sie beginnen, müssen Sie

Stellen Sie eine Verbindung mit Ihrem Business Intelligence-Tool (BI) her:
1. Erstellen eines Leserkontos oder einer Verbindung für Snowflake
2. Herstellen einer Verbindung mit Workfront Data Connect

Sobald Sie eine Verbindung hergestellt haben, können Sie die Abfragen in diesem Artikel verwenden, um Daten zu extrahieren und zu visualisieren.

Anmeldeereignisse der Benutzer von Home-Teams

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
    uc.hometeamid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM users_current uc
    INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(ul.lastlogindate)

Anmeldeereignisse der Home-Teams: Aufschlüsselung

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
    uc.hometeamid,
    uc.userid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM users_current uc
    INNER JOIN userlogins ul ON uc.userid = ul.userid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(ul.lastlogindate)

Anmeldeereignisse der Team-Benutzer

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
    tmc.teamid,
    TO_DATE(ul.lastlogindate),
    count(*)
FROM teammembers_current tmc
    inner join teams_current tc on tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
    inner join userlogins ul on tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(ul.lastlogindate)

Anmeldeereignisse der Team-Benutzer: Aufschlüsselung

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 tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate), count(*)
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN userlogins ul ON tmc.userid = ul.userid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(ul.lastlogindate)

Aufgabenstatusänderungsereignisse von Home-Teams

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
    uc.hometeamid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid,  TO_DATE(tsc.begin_effective_timestamp)

Ereignisse zur Änderung des Aufgabenstatus von Home-Teams: Aufschlüsselung

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
    uc.hometeamid,
    uc.userid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tsc.begin_effective_timestamp)

Aufgabenstatus-Änderungsereignisse der Team-Benutzer

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 tmc.teamid,  TO_DATE(tsc.begin_effective_timestamp), count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid,  TO_DATE(tsc.begin_effective_timestamp)

Aufgabenstatus-Änderungsereignisse der Team-Benutzer: Aufschlüsselung

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
    tmc.teamid,
    tmc.userid,
    TO_DATE(tsc.begin_effective_timestamp),
    count(tsc.taskid) as task_status_updates
FROM teammembers_current tmc
    inner join teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    inner join assignments_current ac ON tmc.userid = ac.assignedtoid
    inner join task_status_changes tsc ON ac.taskid = tsc.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tsc.begin_effective_timestamp)

Abschlussereignisse der Aufgaben von Home-Teams

WITH task_status_done 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 te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    uc.hometeamid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, TO_DATE(tasks_done.task_completion_date)

Ereignisse zum Abschluss von Aufgaben der Home-Teams: Aufschlüsselung

WITH task_status_done 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 te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    uc.hometeamid,
    uc.userid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM users_current uc
    INNER JOIN assignments_current ac ON uc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE uc.hometeamid is not null
GROUP BY uc.hometeamid, uc.userid, TO_DATE(tasks_done.task_completion_date)

Abschlussereignisse der Aufgaben von Team-Benutzern

WITH task_status_done 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 te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT
        taskid,
        actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    tmc.teamid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid and tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, TO_DATE(tasks_done.task_completion_date)

Aufgabenabschlussereignisse der Team-Benutzer: Aufschlüsselung

WITH task_status_done 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 te
        WHERE status = 'CPL'
    )
    WHERE status != previous_status
), task_percentage_done as (
    SELECT
        taskid,
        percentcomplete,
        begin_effective_timestamp
    FROM (
        SELECT
            taskid,
            percentcomplete,
            begin_effective_timestamp,
            lag(percentcomplete, 1, '0') OVER (PARTITION BY taskid ORDER BY begin_effective_timestamp) as previous_percent_complete
        FROM tasks_event
        WHERE TO_NUMBER(percentcomplete) = 100
    )
    WHERE percentcomplete != previous_percent_complete
), task_completion_date_exists as (
    SELECT taskid, actualcompletiondate
    FROM tasks_event
    WHERE actualcompletiondate is not null
)

SELECT
    tmc.teamid,
    tmc.userid,
    TO_DATE(tasks_done.task_completion_date),
    count(tasks_done.taskid) as task_completed
FROM teammembers_current tmc
    INNER JOIN teams_current tc ON tc.teamid = tmc.teamid AND tc.teamtype != 'PROJECT'
    INNER JOIN assignments_current ac ON tmc.userid = ac.assignedtoid
    INNER JOIN (
        SELECT distinct taskid, task_completion_date
        FROM (
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_status_done
            UNION
            SELECT taskid, TO_DATE(begin_effective_timestamp) as task_completion_date
            FROM task_percentage_done
            UNION
            SELECT taskid, TO_DATE(actualcompletiondate) as task_completion_date
            FROM task_completion_date_exists
        )
    ) tasks_done ON ac.taskid = tasks_done.taskid
WHERE tmc.teamid is not null
GROUP BY tmc.teamid, tmc.userid, TO_DATE(tasks_done.task_completion_date)

recommendation-more-help

5f00cc6b-2202-40d6-bcd0-3ee0c2316b43