| || |
Excel Query - defect question
Need assistance in my Excel Query.
I need to be able to figure out at each change in status who the defect was assigned to. I figured out most of it but not that. HELP Please
AU_ENTITY_ID 'Defect ID',
AP_ACTION_ID 'Action ID',
AU_TIME 'Date of Last Change',
GETDATE ( )'Current Date',
DATEDIFF (dd,AU_TIME, GETDATE ( ))'Days Since Status Change', BG_SUMMARY, BG_RESPONSIBLE
FROM AUDIT_PROPERTIES, AUDIT_LOG, BUG
WHERE AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_USER_06'
AND AU_ACTION_ID = AP_ACTION_ID
AND BG_BUG_ID = AU_ENTITY_ID
ORDER BY AU_ENTITY_ID
Re: Excel Query - defect question
It's not obvious because the AUDIT_PROPERTY table only record the fields that have been changed.
You need to retrieve all changes for the BG_STATUS and BG_RESPONSIBLE fields and build the history from that. I'm not sure it's achievable with pure SQL (I mean: you may need to resort to some post-processing in Excel).
BTW, I don't understand why you filter the AUDIT_PROPERTY on BG_USER_06.