SQL Query to Find the Date when the Defect Status is moved to Fixed

by Community Manager on ‎06-03-2012 11:35 AM

Question

Hello Experts,

I am generating excel report to find the Date when the Defect is moved to "Fixed" Status.

 

Bascially, I need to show in the report the number of days development team took to fix the defect. (Date on Status moved to "Fixed" minus Defect Creation Date)

And 

Number of Days testers took to retest it and change the status to "Closed". 

 

Did someone has similar kind of requirement.

 

I know it can be done thru Audit tables. . . . But basically i am not good at SQL.

 

Any help is greatly appreciated.

Answer

You can use below query in Excel Generator

 

SELECT

ONE.DEFECT_ID,
ONE.STATUS_NEW,
ONE.UserName_New,
TWO.STATUS_FIXED,
TWO.UserName_Fixed,
THREE.STATUS_CLOSED,
THREE.UserName_Closed

FROM


(SELECT
AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
MIN(AUDIT_LOG.AU_TIME) AS STATUS_NEW,
AUDIT_LOG.AU_USER AS UserName_New
FROM
AUDIT_LOG INNER JOIN AUDIT_PROPERTIES
ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' AND AUDIT_LOG.AU_ACTION = 'UPDATE' AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'New'
GROUP BY
AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER) ONE,

 


(SELECT
AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
MAX(AUDIT_LOG.AU_TIME) AS STATUS_FIXED,
AUDIT_LOG.AU_USER AS UserName_Fixed
FROM
AUDIT_LOG INNER JOIN AUDIT_PROPERTIES
ON AUDIT_LOG.AU_ACTION_ID =AUDIT_PROPERTIES.AP_ACTION_ID WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' AND AUDIT_LOG.AU_ACTION = 'UPDATE' AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Fixed'
GROUP BY
AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER) TWO,


(SELECT
AUDIT_LOG.AU_ENTITY_ID AS DEFECT_ID,
MAX(AUDIT_LOG.AU_TIME) AS STATUS_CLOSED, AUDIT_LOG.AU_USER AS UserName_Closed FROM AUDIT_LOG INNER JOIN AUDIT_PROPERTIES ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID WHERE AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' AND AUDIT_LOG.AU_ACTION = 'UPDATE' AND AUDIT_PROPERTIES.AP_TABLE_NAME = 'BUG' AND AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' AND AUDIT_PROPERTIES.AP_NEW_VALUE = 'Closed'
GROUP BY
AUDIT_LOG.AU_ENTITY_ID,AUDIT_LOG.AU_USER) THREE,


BUG

WHERE

ONE.DEFECT_ID = TWO.DEFECT_ID AND
TWO.DEFECT_ID = THREE.DEFECT_ID AND
THREE.DEFECT_ID = BUG.BG_BUG_ID
Order By BG_BUG_ID

Comments
by jodybuchanan on ‎08-14-2013 07:27 AM

This query has really help open up the data in QC.  Is there another location/website for more sample queries?

by on ‎11-29-2013 06:01 AM

Hello you can check this post from my blog.

 

Regards.

by JinKim on ‎08-13-2014 05:56 PM

Thank you Kevin_Paul for your query.

That is what I want to make it to get 'who/when closed a defect'

However, the query returned all closed dates for same defect ID.

Becaue the defect status was changed to Fixed again from Closed.(twice closed)

 

How can I pick a row up which the defect status was to 'Closed' lastly?

Search
Showing results for 
Search instead for 
Do you mean 
Follow Us


HP Blog

HP Software Solutions Blog

The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation