Project and Portfolio Management Practitioners Forum
cancel

Waiting time in Particular request status

Highlighted
Shravan Kathuri
Honored Contributor.

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
Acclaimed 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
Honored Contributor.

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
Acclaimed 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