Audit trail of workflow approvers

Hi All,I need to create livereport which needs to show users when a task is accepted and done by a approver; also when a task is pending with approver.I wrote below sql for completed workflow tasks but it shows negative values in timetaken field. Could someone tell me where i went wrong in below query.Select w.WorkAudit_TaskID,w.WorkAudit_date, w.workaudit_performerid,v.workaudit_date,v.WorkAudit_PerformerID, datediff(minute,w.WorkAudit_date,v.workaudit_date) as Timetaken FROM wworkaudit w, wworkaudit vx.workaudit_workid = v.workaudit_workid and and x.workaudit_status = 21 and v.workaudit_Status = 29 and x.workaudit_performerid is not nulland v.workaudit_performerid is not null and x.workaudit_performerid <> v.workaudit_performeridorder by timetakenThanks in advance.

Find more posts tagged with

Comments

Lindsay_Davies

Message from Lindsay Davies <ldavies@opentext.com> via eLink

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">eLink

Hi Jyothi,

I think you did not paste in a working statement.

You select with alias w but later this is referred to as x;

there is an extra AND ;

a missing WHERE.

However, making the assumption that essentially this is what you have working, then here are my comments....

You are not making sure the w(x) row is the predecessor for the v row, chronologically.

So you probably get more rows out than you would expect.

You need to order the rows by audit date (workaudit_date) as you process them.

Then if you want the final results in a different order, just do another select from the result-set with an alias.

I have not written any queries to achieve this sort of thing with workflow audits, but have written similar queries on other tables.

Instead of doing a self join I wrote a query using a more advanced function, creating a ranking query using the OVER clause.

How you write that depends on the RDBMS, I think.

Are you Oracle or SQL server?

Regards

Lindsay

Open Text UK

From: eLink Discussion: Live Reports Discussion [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: 2011 July 25, Mon 10:20
To: eLink Recipient
Subject: Audit trail of workflow approvers

Audit trail of workflow approvers

Posted by jyothi.mattaparthi@philips.com (user, Philips) on 2011/07/25 05:17

Hi All,

I need to create livereport which needs to show users when a task is accepted and done by a approver; also when a task is pending with approver.

I wrote below sql for completed workflow tasks but it shows negative values in timetaken field. Could someone tell me where i went wrong in below query.

Select w.WorkAudit_TaskID,w.WorkAudit_date, w.workaudit_performerid,v.workaudit_date,v.WorkAudit_PerformerID, datediff(minute,w.WorkAudit_date,v.workaudit_date) as Timetaken FROM wworkaudit w, wworkaudit v
x.workaudit_workid = v.workaudit_workid and
and x.workaudit_status = 21 and v.workaudit_Status = 29 and x.workaudit_performerid is not null
and v.workaudit_performerid is not null
and x.workaudit_performerid <> v.workaudit_performerid
order by timetaken

Thanks in advance.