Tuesday, June 4, 2013

Getting defect time report for status change actions

In the analysis module of the QC there are bunch of report types that you can measure what is going on about the defects for your project. But some reports are missing. I'll describe one of the most important one.
If you want to see how much time spent for fixing a defect you have to check it's history section from the GUI. There is no way - at least i couldn't find - for taking this information from the predefined reports for all (or a group of) defects. Here is the SQL for how many hours passed for changing a defect's status (Closed and Rejected&Closed statuses are assumed to be final statuses for a defect). You can use these SQLs in the Analysis section by creating Excel Report or you may want to use them directly as a SQL statement via TOAD or you can embed them in an excel sheet.



SELECT TO_NUMBER(AAA.BUG_ID) ID,
       AAA.AU_TIME STARTDT,
       BBB.AU_TIME ENDDT,
       ROUND(((BBB.AU_TIME - AAA.AU_TIME) * 24),2) TIME_ELAPSED_HOURS,
       BBB.AP_OLD_VALUE || '>' || BBB.AP_NEW_VALUE ACTION,
       TO_CHAR(CCC.BG_DETECTION_DATE, 'yyyy-mm') TERM,
       CCC.BG_SEVERITY
FROM   (SELECT ROWNUM ROW_ID, AA.*
        FROM   (SELECT A.AU_ENTITY_ID BUG_ID,
                       A.AU_TIME,
                       B.AP_OLD_VALUE,
                       B.AP_NEW_VALUE
                FROM   <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_LOG A,
                       <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_PROPERTIES B
                WHERE  A.AU_ACTION_ID = B.AP_ACTION_ID AND
                       A.AU_ENTITY_TYPE = 'BUG' AND
                       B.AP_FIELD_NAME = 'BG_STATUS'
                ORDER BY A.AU_ENTITY_ID, A.AU_TIME) AA) AAA,
       (SELECT ROWNUM ROW_ID, AA.*
        FROM   (SELECT A.AU_ENTITY_ID BUG_ID,
                       A.AU_TIME,
                       B.AP_OLD_VALUE,
                       B.AP_NEW_VALUE
                FROM   <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_LOG A,
                       <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_PROPERTIES B
                WHERE  A.AU_ACTION_ID = B.AP_ACTION_ID AND
                       A.AU_ENTITY_TYPE = 'BUG' AND
                       B.AP_FIELD_NAME = 'BG_STATUS'
                ORDER BY A.AU_ENTITY_ID, A.AU_TIME) AA) BBB,
       <<<YOUR_PROJECT_SCHEMA>>>.BUG CCC
WHERE  AAA.BUG_ID = BBB.BUG_ID AND
       (AAA.ROW_ID + 1) = BBB.ROW_ID AND
       CCC.BG_BUG_ID = BBB.BUG_ID
UNION ALL
SELECT TO_NUMBER(CC.BG_BUG_ID) ID,
       DD.AU_TIME STARTDT,
       SYSDATE ENDDT,
       ROUND(((SYSDATE - DD.AU_TIME) * 24), 2) TIME_ELAPSED_HOURS,
       CONCAT('>', CC.BG_STATUS) ACTION,
       TO_CHAR(CC.BG_DETECTION_DATE, 'yyyy-mm') TERM,
       CC.BG_SEVERITY
FROM <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_PROPERTIES BB,
       (SELECT MAX(AU_ACTION_ID) AU_ACTION_ID, BUG_ID
        FROM   (SELECT A.AU_ENTITY_ID BUG_ID,
                       A.AU_ACTION,
                       A.AU_ACTION_ID,
                       A.AU_TIME,
                       B.AP_OLD_VALUE,
                       B.AP_NEW_VALUE
                FROM <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_LOG A,
                       <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_PROPERTIES B
                WHERE  A.AU_ACTION_ID = B.AP_ACTION_ID AND
                       A.AU_ENTITY_TYPE = 'BUG' AND
                       B.AP_FIELD_NAME = 'BG_STATUS'
                ORDER BY A.AU_ENTITY_ID, A.AU_TIME)
        GROUP BY BUG_ID) AA,
       <<<YOUR_PROJECT_SCHEMA>>>.BUG CC,
       <<<YOUR_PROJECT_SCHEMA>>>.AUDIT_LOG DD
WHERE  AA.AU_ACTION_ID = BB.AP_ACTION_ID AND
       AA.BUG_ID = CC.BG_BUG_ID AND
       CC.BG_STATUS NOT IN ('Closed', 'Rejected&Closed') AND
       DD.AU_ACTION_ID = AA.AU_ACTION_ID AND
       DD.AU_ENTITY_TYPE = 'BUG' AND
       BB.AP_FIELD_NAME = 'BG_STATUS'

This SQL will output the status change actions for all defects for the action that is shown in the ACTION column. For example if the ACTION column is New>Open this means TIME_ELAPSED_HOURS column shows how many hours passed until the corresponding defect status changed to "Open" from "New".
It is a good idea to create a View by using this SQL.

To get overall report and transpose from this SQL you can use following statement:

SELECT ID AS BUG_ID,
   SUM("New>Open") "New>Open",
   SUM("Open>Fixed") "Open>Fixed",
   SUM("Open>Rejected") "Open>Rejected",
   SUM("Fixed>Closed") "Fixed>Closed",
   SUM("Rejected>Rejecte&Closed") "Rejected>Rejecte&Closed"
FROM (SELECT ID,
 NVL(DECODE(action, 'New>Open', time_elapsed_hours), 0)
     "New>Open",
 NVL(DECODE(action, 'Open>Fixed', time_elapsed_hours), 0)
     "Open>Fixed",
 NVL(DECODE(action, 'Open>Rejected', time_elapsed_hours), 0)
     "Open>Rejected",
 NVL(DECODE(action, 'Fixed>Closed', time_elapsed_hours), 0)
     "Fixed>Closed",
 NVL(DECODE(action, 'Rejected>Rejected&Closed', time_elapsed_hours), 0)
     "Rejected>Rejected&Closed"
        FROM (SELECT ID,
                 SUM(time_elapsed_hours) time_elapsed_hours,
                 action,
                 term,
                 bg_severity
              FROM   (SELECT * FROM <<<MY_VIEW_CREATED_FROM_ABOVE_SQL>>>) 
                GROUP BY ID, action, term, bg_severity))
GROUP BY ID ORDER BY ID

4 comments:

Rajesh said...

How do we establish an SQL connection with QTP? I mean server,db and connection details?

Mehmet said...

Hello Rajesh,

Connection to Oracle DB is described in the following post.

http://qualitesting.blogspot.com.tr/2013/01/qtp-integration-with-oracle-db.html

Unknown said...

Hi and thank you for the script.

One question:

Where can i find my project schema as for the moment i removed the "<<>>" from the script and it is displaying all the defects from DB.

Thank you,

Mehmet said...

Hello Razvan,

If you are running these SQLs from an excel report that is in the analysis view you can remove schema name and the dot ("<<>>."). It is going to be executed for the current project that you logged in.

If you want to execute this report for another QC project you have to put the schema names. You can check the schema names by following query:

select project_name, db_name from <<>>.projects (marked part is being asked during the installation, if you don't know this part your DBA can tell you what is the correct schema name)

Probably your DBA has the necessary rights to execute this query.
And your DBA has to give select permissions to your user (the db_name for your logged in project) on BUG, AUDIT_LOG and AUDIT_PROPERTIES tables.