| || |
Query on AUDIT_LOG not returning latest change using MAX()
I am trying to create an ageing report for bugs in Released to Retest status using below query. Issue is that even though I am using MAX(), I get multiple rows for some defects (Defects that have moved to RTR status more than once). Please see the sample data the query is returning and suggest a solution to retrieve just the latest record.
BUG.BG_RESPONSIBLE as "Assigned to",
bug.bg_severity as "Severity",
AUDIT_LOG.AU_ENTITY_ID AS "Defect ID",
TO_CHAR (MAX(AUDIT_LOG.AU_TIME), 'DD-MON-YYYY HH:MM') AS "Moved to RTR On",
AUDIT_LOG.AU_USER AS "Moved to RTR By",
Round(SYSDATE-AUDIT_LOG.AU_TIME,0) AS "RTR AGE (Days)"
AUDIT_LOG INNER JOIN AUDIT_PROPERTIES ON AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
inner join bug on au_entity_id = cast(bg_bug_id as varchar(200))
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 = 'Released to Retest'
and bug.bg_status = 'Released to Retest'
and bug.bg_detected_in_rcyc = 1003
GROUP BY BUG.BG_RESPONSIBLE, bug.bg_severity, AUDIT_LOG.AU_ENTITY_ID, AUDIT_LOG.AU_TIME, AUDIT_LOG.AU_USER
Order By BUG.BG_RESPONSIBLE, bug.bg_severity, AUDIT_LOG.AU_ENTITY_ID, AUDIT_LOG.AU_TIME, AUDIT_LOG.AU_USER
Assigned to Severity Defect ID Moved to RTR On Moved to RTR By RTR AGE (Days)
A 3 Medium - Workaround available 1478 15-APR-2014 10:04 AA 28
A 3 Medium - Workaround available 1478 18-APR-2014 06:04 AA 26
A 3 Medium - Workaround available 1478 08-MAY-2014 03:05 BB 6
D 3 Medium - Workaround available 1484 17-APR-2014 12:04 CC 27
D 3 Medium - Workaround available 1484 20-APR-2014 11:04 DD 23
D 3 Medium - Workaround available 1484 22-APR-2014 11:04 EE 21
D 3 Medium - Workaround available 1484 09-MAY-2014 05:05 FF 5
G 3 Medium - Workaround available 1523 15-APR-2014 08:04 GG 29
G 3 Medium - Workaround available 1523 23-APR-2014 01:04 HH 21
G 3 Medium - Workaround available 1523 12-MAY-2014 04:05 II 2
The duplicates appear because you group by au_time and au_user; that makes every record unique, so the max function does not apply to a set of rows. Remove au_time and au_user fields from "group by" and "order by". Remove "Moved to RTR By" from the SELECT statement: the same defect can be updated my more than one user; or you could use max(au_user) there.
Also use max(au_time) in the RTR Age formula.
Originally Posted by qarom
That worked. Thanks for your help! Really appreciate it.