SQL queries in Windchill for Task History

[AshleyHolmes]

Can anyone help me with my SQL query?

I have a query built that integrates several tables in order to report on workflow items and document lifecycle: WTUser, WorkItem, PDMLinkProduct,EPMDocument, etc

I have a bunch of reportable information gathered, but I am missing one key piece - the date a workflow task was accepted. I have workflow created date, I have task completion date, I have the who and the what and the where, but I do not have workflow task accepted date.

We discovered that this information is stored in WfAssignmentEventAudit under .timestamp when .actionPerformed=Accepted, but I am unable to get this data table into my query successfully with a JOIN to WfAssignedActivity or or creating a subset, etc.

Any SQL experts out there willing to take a look at how to join up these related tables?

[Marco_Tosin]

Take a look at this article:

Windchill Data Model for Workflow related objects

https://www.ptc.com/en/support/article?n=CS153902

[AshleyHolmes · Accepted solution]

We found a connection for WfAssignmentEventAudit wfaea and WfAssignedActivity wfaa on

wfaea.activityKey = wfaa.wtkey

and used

wfaa.startTime  -- when the task became available in the grp inbox
wfaea.timestamp -- when the task was ACCEPTED where userID is !=null and .actionPerformed = accepted

and
wfaa.endTime  -- when the task was completed