Getting Archived Date for Workflows
I am having trouble with a LiveReport for Archived Workflows. I have a report that pulls title, initiated date and completed date and calculated a duration that works fine. It gives me the information I ask for, from the workflow templates I want, within a specific date range. However, many of the workflows never reach Completed status --they are simply stopped and Archived. I would like to add the Archived date to my existing report, and calculate a duration from initiation to archival. The problem is, there isn't any column for workflow archived date in the database --it must be derived (I believe) from the WWorkAudit table. I have tried, but I am unable to get the archived date. Can someone help me get the SQL right to show the archived date? Here is the SQL that works: SELECT SubWork_Title TITLE, to_char (Work_DateInitiated,'MM/DD/YYYY') INITIATED, to_char (Work_DateCompleted,'MM/DD/YYYY') COMPLETED, (Work_DateCompleted-Work_DateInitiated+1) "#DAYS DURATION" FROM WWork, WSubWork, DTree, WMap WHERE (dtree.dated=wrap.map_mapobjid) AND (WSubWork.SubWork_WorkID=WWork.Work_WorkID) AND subwork_mapid=map_mapid AND dtree.dataid IN('977166', '977341', '1867754', '1255850', '1257044', '2585978') AND work_dateinitiated >= %1 AND work_dateinitiated <= %2 AND Work_Status= -3ORDER BY Work_DateInitiated %1 is user input, date%2 is user input, date --------------------------------------------Here is the SQL with a subquery added to pull archived date, which DOES NOT work: SELECT SubWork_Title TITLE, to_char (Work_DateInitiated,'MM/DD/YYYY') INITIATED, to_char (Work_DateCompleted,'MM/DD/YYYY') COMPLETED, (SELECT (to_char (WorkAudit_Date,'MM/DD/YYYY')) FROM WWorkAudit w1 WHERE WWorkAudit.WorkAudit_Status=6 AND w1.WorkAudit_WorkID=WWorkAudit.WorkAudit_WorkID) ARCHIVED,(Work_DateCompleted-Work_DateInitiated+1) "#DAYS DURATION" FROM WWork, WSubWork, DTree, WMap WHERE (dtree.dataid=wmap.map_mapobjid) AND (WSubWork.SubWork_WorkID=WWork.Work_WorkID) AND subwork_mapid=map_mapid AND dtree.dataid IN('977166', '977341', '1867754', '1255850', '1257044', '2585978') AND work_dateinitiated >= %1 AND work_dateinitiated <= %2 AND Work_Status= -3ORDER BY Work_DateInitiated %1 is user input, date%2 is user input, dateAny help to get the archived date would be greatly appreciated.
