Highlighted
Established Member..
Established Member..
4306 views

SQL Query need for BUG History

Hello,

I need an sql query to caluclate the number of days that a bug moved from Status = "Open" to Status = "Fixed".

 

Bascially two teams work on bug, How many days development team took to fix the bug and How many days the testing team took to change the status from Fixed to Closed.

 

Now the query should display the total number of days of each bug status.

 

CAn somebody help me with the query.

 

Thanks

BK

0 Likes
3 Replies
Absent Member.
Absent Member.

Bug histories are stored in AUDIT_PROPERTIES & AUDIT_LOG  tables. You can use these two tables.

0 Likes
Highlighted
Established Member..
Established Member..

Thanks for the reply...

I have the below query with Audit Log and Audit Properties.

It works fine to get the status of the bug. . . .Now how do i calculate the date ???????

 

SELECT
AU_ENTITY_ID AS "BUG ID",
AP_OLD_VALUE AS "OLD VALUE",
AP_NEW_VALUE AS "NEW VALUE",
AU_USER AS "USER",
AU_TIME AS "CHANGE TIME"
FROM
AUDIT_PROPERTIES
INNER JOIN AUDIT_LOG ON AP_ACTION_ID = AU_ACTION_ID
WHERE
AU_ACTION='UPDATE'
AND AP_TABLE_NAME='BUG'
AND AP_FIELD_NAME='BG_STATUS'
AND AP_OLD_VALUE IN('Open', 'Fixed')
AND AP_NEW_VALUE IN ('Fixed', 'Closed')
Order by AU_ENTITY_ID

Highlighted
Absent Member.. Absent Member..
Absent Member..

In this case I use function

Function returns a date when status 

You need to call this function with your select clause

 

CREATE FUNCTION [dbo].[GetStatusTD]
(@i_ap_action_id int, @i_au_time datetime, @i_bug_id varchar(200))
RETURNS datetime
-- Возвращает дату, до которой живет статус (FD=audit_log.AU_TIME, а TD=audit_log.AU_TIME у следующей записи)
-- v 1.0
AS
BEGIN

declare
@o_td datetime;

declare q_bug_log cursor for
select
al.au_time
from audit_log al, audit_properties ap
where 1=1
and al.au_entity_type='BUG'
and ap.ap_table_name='BUG'
and ap.ap_field_name='BG_STATUS'
and ap.ap_action_id=al.au_action_id
and al.au_entity_id=@i_bug_id
and al.au_time>@i_au_time
order by al.au_time asc;

open q_bug_log;
FETCH next FROM q_bug_log into @o_td;

if @o_td is not null set @o_td=dateadd(ms, -1, @o_td) else set @o_td=cast ('2020-01-01' as datetime)

CLOSE q_bug_log
DEALLOCATE q_bug_log

RETURN @o_td;
END;

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.