專案樹狀圖查詢

Last update: Fri Apr 25 2025 00:00:00 GMT+0000 (Coordinated Universal Time)

主題：

建立對象：

使用者

您可以使用本文中的查詢來建立類似於Enhanced Analytics中的資料視覺效果。

IMPORTANT

查詢會產生類似於「增強型Analytics」中所顯示的結果，但可能不會完全相符。

先決條件

開始之前，您必須

與您的Business Intelligence (BI)工具建立連線：
1. 建立Snowflake的讀取器帳戶或連線
2. 建立與Workfront資料連線的連線

建立連線後，您可以使用本文中的查詢來擷取及視覺化資料。

已淘汰專案計畫時數

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        workrequired,
        percentcomplete,
        calendardate,
        (workrequired - (workrequired * percentcomplete)) as remainingMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.workrequired) as projectTotalWork,
    sum(tdw.remainingMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

已淘汰專案計畫時數：待執行工作

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        workrequired,
        percentcomplete,
        calendardate,
        (workrequired - (workrequired * percentcomplete)) as remainingMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.workrequired) as projectTotalWork,
    sum(tdw.remainingMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

已淘汰專案計畫期間

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        planneddurationminutes,
        percentcomplete,
        calendardate,
        (planneddurationminutes - (planneddurationminutes * percentcomplete)) as remainingDurationMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.planneddurationminutes) as projectTotalWork,
    sum(tdw.remainingDurationMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

已淘汰專案計畫期間：待執行工作

WITH task_daily_work as (
    SELECT
        taskid,
        projectid,
        planneddurationminutes,
        percentcomplete,
        calendardate,
        (planneddurationminutes - (planneddurationminutes * percentcomplete)) as remainingDurationMinutes
    FROM tasks_daily_history
)

SELECT
    p.name,
    p.projectid,
    sum(tdw.planneddurationminutes) as projectTotalWork,
    sum(tdw.remainingDurationMinutes) as projectRemainingWork,
    tdw.calendardate
FROM projects_current p
    JOIN task_daily_work tdw ON p.projectid = tdw.projectid
GROUP BY p.projectid, p.name, tdw.calendardate

recommendation-more-help

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