query to get the durations of Change
select c.CHANGE_NUMBER,
(select le.ENTRYVALUE from listentry le
where le.ENTRYID = c.CATEGORY)as CATEGORY ,
(
SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.ORIGINATOR
) initiator,(
SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.owner
) coordinator, w.state,
(
SELECT description
FROM nodetable
WHERE id = w.state
) workflow_status , TO_CHAR(w.LOCAL_DATE,’DD-MON-YY HH24:MI:SS’)local_date,
(select
(trunc(to_date(B.local_date, ‘DD-MON-YY HH24:MI:SS’) – to_date(w.local_date, ‘DD-MON-YY HH24:MI:SS’)))as timediff
FROM WORKFLOW_PROCESS w INNER JOIN WORKFLOW_PROCESS B ON B.order_by = (w.order_by + 1)
where w.change_id=B.Change_id and w.change_id=9946301 ORDER BY w.order_by ASC )
from change c, WORKFLOW_PROCESS w
where w.change_id = c.id– order by w.ORDER_BY)
and c.CHANGE_NUMBER=’NPI001084′
order by w.ORDER_BY,local_date ASC
This subquery “(select
(trunc(to_date(B.local_date, ‘DD-MON-YY HH24:MI:SS’) – to_date(w.local_date, ‘DD-MON-YY HH24:MI:SS’)))as timediff
FROM WORKFLOW_PROCESS w INNER JOIN WORKFLOW_PROCESS B ON B.order_by = (w.order_by + 1)
where w.change_id=B.Change_id and w.change_id=9946301 ORDER BY w.order_by ASC )” returns multiple rows.. hence I am getting error adding to the main query.
Nagma,
I hope I have an acceptable solution for you. You didn’t answer my last question so I made some assumptions.
The ‘DIFF’ column gives you the accumulated time from the first step in the workflow. I believe you can do more with the formatting using the EXTRACT function, but for now, here’s an example of DIFF format: “+00 02:27:52.000000”. I assume if it went into days, then those first 2 zeros would reflect that. You notice that I removed your questionable sub-query altogether. Instead, I did a LEFT JOIN of WORKFLOW_PROCESS. There is some explanation in the comments.
Steve LaLonde
Try running the following query:
select c.CHANGE_NUMBER
,(select le.ENTRYVALUE from listentry le where le.ENTRYID = c.CATEGORY)as CATEGORY
,(SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.ORIGINATOR) initiator
,(SELECT last_name || ‘,’ || first_name || ‘(‘ || loginid || ‘)’
FROM agileuser
WHERE id = c.owner) coordinator
, w1.state
,(SELECT description
FROM nodetable
WHERE id = w1.state) workflow_status
, w1.ORDER_BY
, NVL(TO_CHAR(w1.LOCAL_DATE,’DD-MON-YY HH24:MI:SS’),0)local_date
, NVL(TO_CHAR(w2.LOCAL_DATE,’DD-MON-YY HH24:MI:SS’),0)INITIAL_LOCAL_DATE
,(CAST(w1.LOCAL_DATE as timestamp) – CAST(w2.LOCAL_DATE as timestamp)) diff
from WORKFLOW_PROCESS w1
INNER JOIN CHANGE C ON W1.CHANGE_ID = C.ID
–BY DOING THE FOLLOWING JOIN WHERE ORDER_BY = 0, WE GET THE SAME DATE/TIME REPEATEDLY FOR THE FIRST STEP IN THE WORKFLOW (INITIAL_LOCAL_DATE).
–BY DOING THIS, WE CAN ACCUMMULATE THE TIME RATHER THAN TRY TO GET THE TIME FROM ONE STEP TO THE NEXT.
LEFT JOIN WORKFLOW_PROCESS W2 ON W2.CHANGE_ID = C.ID AND W2.ORDER_BY = 0
WHERE c.CHANGE_NUMBER=’NPI001084′
–THIS NEXT LINE MAY NOT BE REQUIRED IN YOUR CASE. IN MY TEST CASE, WHEN THE CHANGE WENT TO A STATUS OF Implemented,
–THE LOCAL DATE WAS NULL. I AM NOT SURE IF THAT HAPPENS IN ALL CASES
AND W1.LOCAL_DATE IS NOT NULL
order by w1.ORDER_BY,local_date ASC;