PDMLink plan related SQL statement query

[AZ_10746014]

I am using Windchill PDMLink Release 11.0 and Datecode with CPS N/A

The user hopes to obtain Plan-related data and present visual charts. How to query the following results through SQL statements:
1. Plan’s %Work Complete
2. Plan the completion quantity percentage of the lower-level activities of each first-level activity (or you can also query the completion status and calculate the percentage through Code)
3. Under each first-level activity of Plan, the percentage of projects to be delivered and projects delivered (or you can also check the delivery status and calculate the percentage through Code)

Please help, thank you! !

Here are the errors that I faced
Problems encountered: Through SQL: SELECT A0.classnameA2A2,A0.idA2A2,A0.name,A0.classnameA2A2,A0.idA2A2,A0.percentWorkComplete,A0.healthStatusType,A0.riskType,TO_CHAR(A0.finishDate,'dd mm yyyy hh24:mi:ss'),A0.plannableState,A0.classnameA2A2,A0.idA2A2,A1.name FROM PlanActivity A0,EPPlan A1 WHERE ((UPPER(A1.name) = ?)) AND (A0.idA3parentReference = A1 .idA2A2) order by A0.ida2a2 The Plan %Work Complete result queried is inconsistent with what is seen on the page.

Translated by the Community Moderation using Google Translate
-----------------------------------------------------------------------------------------------

I am using Windchill PDMLink Release 11.0 and Datecode with CPS N/A

用戶希望通過獲取Plan的相關數據，呈現可視化圖表，如何通過SQL語句，查詢到以下結果：
1. Plan的%Work Complete
2. Plan每一個一階活動的下階活動 完成數量百分比（或者查詢出完成情況也可，通過Code計算百分比）
3. Plan每一個一階活動下 待交付項目與已交付項目百分比（或者查出交付情況也可，通過Code計算百分比）

請協助，謝謝！！

Here are the errors that I faced
遇到的問題：通過SQL: SELECT A0.classnameA2A2,A0.idA2A2,A0.name,A0.classnameA2A2,A0.idA2A2,A0.percentWorkComplete,A0.healthStatusType,A0.riskType,TO_CHAR(A0.finishDate,'dd mm yyyy hh24:mi:ss'),A0.plannableState,A0.classnameA2A2,A0.idA2A2,A1.name FROM PlanActivity A0,EPPlan A1 WHERE ((UPPER(A1.name) = ?)) AND (A0.idA3parentReference = A1.idA2A2) order by A0.ida2a2 查詢出來的Plan %Work Complete結果與頁面中看到的不一致。

[Rocket_Max · Accepted solution]

Hi @AZ_10746014 

Windchill has a similar report

[AZ_10746014]

But through this report, I still can’t get the SQL I want. Can you share the relevant SQL statements with me? Thank you.

Translated by the Community Moderation using Google Translate
-----------------------------------------------------------------------------------------------

但是通過這個報表，還是沒辦法獲取想要的SQL，能分享相關的SQL語句給我嗎，謝謝

[Rocket_Max]

Hi @AZ_10746014 

SELECT
a0.percentworkcomplete,
to_char(a0.startdate, 'dd mm yyyy hh24:mi:ss'),
to_char(a0.actualstartdate, 'dd mm yyyy hh24:mi:ss'),
to_char(a0.finishdate, 'dd mm yyyy hh24:mi:ss'),
to_char(a0.actualfinishdate, 'dd mm yyyy hh24:mi:ss'),
a0.totalcost,
a0.name,
a0.plannablestate,
a0.classnamea2a2,
a0.ida2a2,
a0.classnamea2a2,
a0.ida2a2,
a1.namecontainerinfo,
a0.risktype,
a0.healthstatustype,
a0.percentworkcomplete,
a0.classnamea2a2,
a0.ida2a2,
( a0.millisduration ) / ( '28800000' ),
( a0.millistotaleffort ) / ( '3600000' ),
( a0.millisdoneeffort ) / ( '3600000' ),
( a0.totalcost ) - ( a0.fixedcost )
FROM
epplan a0,
(
SELECT
a1.ida2a2,
a1.namecontainerinfo
FROM
exchangecontainer a1
UNION ALL
SELECT
a1.ida2a2,
a1.namecontainerinfo
FROM
orgcontainer a1
UNION ALL
SELECT
a1.ida2a2,
a1.namecontainerinfo
FROM
project2 a1
) a1
WHERE
a0.ida3containerreference = a1.ida2a2

[AZ_10746014]

通過這個SQL 搜索出來的結果為空。project2表為空