Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Waiting time in Particular request status

Highlighted
Shravan Kathuri
Senior Member

Waiting time in Particular request status

Can any one give the SQL query to find out the
waiting time of the request at particular status in workflow?( i.e, how many days the request is stayed at particular status)

Thanks
Shravan kumar.K
3 REPLIES
Mahen M
Honored Contributor

Re: Waiting time in Particular request status

Hi,
Please do some modification to the attached Query to get the desired results.

Regards,
Mahendran M
Shravan Kathuri
Senior Member

Re: Waiting time in Particular request status

HI Mahendran,Thanks for your response. We have 3 decision steps with same statuses. I want to have query for finding out the waiting time of the request for this status.
Could you please provide the query If you have.

Thanks
Shravan kumar.K

Mahen M
Honored Contributor

Re: Waiting time in Particular request status

Hi Sharvan,

(select max(creation_date) from prodpitg.KWFL_STEP_TRANSACTION_HISTORY
where status = 'ELIGIBLE' and
step_transaction_id = (select max(step_transaction_id) from prodpitg.KCRT_STEP_TRANSITIONS_V where request_id =rqst.request_id and sort_order = 1))

The above Query gives the date in which the Request came active to Step number 1.

-----------------------

(select max(creation_date)
from prodpitg.KWFL_STEP_TRANSACTION_HISTORY
where status = 'COMPLETE' and
step_transaction_id = (select max(step_transaction_id) from prodpitg.KCRT_STEP_TRANSITIONS_V where request_id =rqst.request_id and sort_order = 1))

The above Query gives us the date in which the step number 1 is completed.

You have to find the difference between the 2 dates according to your requirement.

If you have the same status in 3 decision steps then you have to sum up the number after converting the Dates to Number (to_number in Oracle)

Even you can modify the above Query so that the Query cost is low.

Regards,
Mahendran M
//Add this to "OnDomLoad" event