Good sql queries using Excel Reports (6564 Views)
Reply
Advisor
Yisroel Orenbuch
Posts: 18
Registered: ‎02-11-2008
Message 1 of 11 (6,564 Views)

Good sql queries using Excel Reports

I have attached 2 sql queries that use all system fields to generate 2 sets of data that can be used to make very useful Defect Trend graphs. I have attached the queries [I wrote them in QC 9.2 with an Oracle db, so if anyone can translate it to sql server, I'd love to see it work there too]. I have also attached a template of the Defect Trend Graph that uses the 2 sets of data, so you can see how they can be used. Just be aware that the Defect Trend Graph that is attached was generated off of an earlier version of this that had to have the cycle start date information [or dates of Releases to QA] entered manually.

* I'm working on the vba code to automate the creation of the graphs [my old method was to open a template xls file with the graph already created, and just push the new data to it]. When its ready I will share that as well.

* It can be used with other fields besides the Status.

* It can be used with Outstanding Requirements, but my team is using only the Defects module at the moment.

Let me know what you think.
Advisor
Yisroel Orenbuch
Posts: 18
Registered: ‎02-11-2008
Message 2 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

I dont think the zip file attached properly - Here are the sql queries
Advisor
Yisroel Orenbuch
Posts: 18
Registered: ‎02-11-2008
Message 3 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

That didnt work either - I have pasted the queries below:
Parameters
@OFFSET@ - Optional; if not used, you will get the data on the 30 most recent days. If you used, the set the starting point of the results further back. Use integers.
@RELEASE_NAME@ - Name of the Release from the Release Module, for Outstanding Defects it looks at Target Release, for Total Defects Submitted it looks at Detected in Release. This was my best guess.
@OUTSTANDING_STATUSES@ - statuses that are outstanding i.e. 'New', 'Open, ...



Query - Outstanding Defects:
Select CycName, CalDt,
Count(PRIORITY_C) as Critical,
Count(PRIORITY_H) as High,
Count(PRIORITY_M) as Medium,
Count(PRIORITY_L) as Low,
Count(PRIORITY_MERGED) as Total From
(Select CalDt,
Case when PRIORITY_MERGED = 'Critical' then 'Critical' end as PRIORITY_C,
Case when PRIORITY_MERGED = 'High' then 'High' end as PRIORITY_H,
Case when PRIORITY_MERGED = 'Medium' then 'Medium' end as PRIORITY_M,
Case when PRIORITY_MERGED = 'Low' then 'Low' end as PRIORITY_L,
PRIORITY_MERGED
From (Select CalDt, BG_BUG_ID, PRIORITY_MERGED, Max(AU_TIME) as Latest_Change From
(Select CalDt, BG_BUG_ID, PRIORITY_MERGED, AU_TIME FROM
(select trunc(current_date - @OFFSET@) - 1*(level-1) CalDt from DUAL connect by level <= 30)
left join
(Select BG_BUG_ID,
AU_TIME,
Case when BUG.BG_PRIORITY is not Null then BUG.BG_PRIORITY
Else BUG.BG_SEVERITY End as PRIORITY_MERGED
from AUDIT_LOG, AUDIT_PROPERTIES, BUG
Where AU_ENTITY_TYPE = 'BUG'
And AU_ENTITY_ID = BG_BUG_ID
And AU_ACTION_ID = AP_ACTION_ID
AND AP_FIELD_NAME = 'BG_STATUS'
AND BG_TARGET_REL = (SELECT REL_ID FROM RELEASES Where REL_NAME = '@RELEASE_NAME@')
)
On trunc(AU_TIME) <= CalDt)
Group By CalDt, BG_BUG_ID, PRIORITY_MERGED) results, AUDIT_LOG, AUDIT_PROPERTIES

where AU_ENTITY_TYPE = 'BUG'
AND AP_FIELD_NAME = 'BG_STATUS'
AND AU_ENTITY_ID = results.BG_BUG_ID
AND AU_TIME = results.Latest_Change
AND AU_ACTION_ID = AP_ACTION_ID
AND AP_NEW_VALUE in (@OUTSTANDING_STATUSES@)
)

left join
(Select RCYC_NAME as CycName, trunc(RCYC_START_DATE) as CycDt from RELEASE_CYCLES
where RELEASE_CYCLES.RCYC_PARENT_ID =(SELECT RELEASES.REL_ID FROM
RELEASES Where RELEASES.REL_NAME = '@RELEASE_NAME@')
)
On CycDt = CalDt
Group by CalDt, CycName
Order By CalDt Asc

Query - Total Defects Reported
Select CycName, CalDt,
Count(PRIORITY_C) as Critical,
Count(PRIORITY_H) as High,
Count(PRIORITY_M) as Medium,
Count(PRIORITY_L) as Low,
Count(PRIORITY_MERGED) as Total
From
(Select CalDt,
Case when PRIORITY_MERGED = 'Critical' then 'Critical' end as PRIORITY_C,
Case when PRIORITY_MERGED = 'High' then 'High' end as PRIORITY_H,
Case when PRIORITY_MERGED = 'Medium' then 'Medium' end as PRIORITY_M,
Case when PRIORITY_MERGED = 'Low' then 'Low' end as PRIORITY_L,
PRIORITY_MERGED
From
(Select CalDt, PRIORITY_MERGED From
(select trunc(current_date - @OFFSET@) + -1*(level-1) CalDt from DUAL connect by level <= (current_date - @OFFSET@) - ((Select REL_START_DATE from RELEASES Where REL_NAME = '@RELEASE_NAME@') -1))
left join

(SELECT trunc(BG_DETECTION_DATE) as SubDate,
Case when BG_PRIORITY is not Null then BG_PRIORITY Else BG_SEVERITY End as
PRIORITY_MERGED
from BUG
Where
BG_DETECTED_IN_REL = (SELECT REL_ID FROM RELEASES Where REL_NAME = '@RELEASE_NAME@'))
On SubDate = CalDt)
)
left join
(Select RCYC_NAME as CycName, trunc(RCYC_START_DATE) as CycDt from RELEASE_CYCLES
where RELEASE_CYCLES.RCYC_PARENT_ID =(SELECT RELEASES.REL_ID FROM
RELEASES Where RELEASES.REL_NAME = '@RELEASE_NAME@')
)
On CycDt = CalDt
Group by CalDt, CycName
Order By CalDt Asc
Advisor
DevonK
Posts: 18
Registered: ‎10-22-2008
Message 4 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

Hi Yisroel,

Thank you for your post. I didn't run the queries, but sharing such ideas is a great idea. Lots of good code in these forums.

Sincerely,
Devon
Occasional Advisor
QAAutomation
Posts: 16
Registered: ‎01-20-2010
Message 5 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

Any possibility what will it be the MSSQL equivaluent for the given queries. I was looking in MSSQL d/b tables and I don't see any table named as DUAL. Also what we mean by "connect by level". Sorry! as I am not good @ oracle SQLs.

Thanks!
Advisor
Yisroel Orenbuch
Posts: 18
Registered: ‎02-11-2008
Message 6 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

DUAL is a specific to Oracle 'dummy' table; there are ways of doing the same things in other sql flavors, but I dont know what they are, since I was only focused on getting this to work at my location [and we use Oracle]. Connect By Level is an Oracle specific syntax as well. I would love to see someone translate this. Good luck.
Occasional Advisor
QAAutomation
Posts: 16
Registered: ‎01-20-2010
Message 7 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

I am assuming QC table names & fields are same for both d/s Oracle & MSSQL. Can anyone please confirm if that is a valid assumption
Occasional Advisor
Panka
Posts: 8
Registered: ‎03-31-2010
Message 8 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports

Hi, Yisroel,
I am trying to do something similiar in my environment (Oracle), and find your query very useful as a start.
Can you advice me how can I set the default values for the 3 parameters.
Advisor
Yisroel Orenbuch
Posts: 18
Registered: ‎02-11-2008
Message 9 of 11 (6,554 Views)

Re: Good sql queries using Excel Reports


Parameters
@OFFSET@ - Optional; if not used, you will get the data on the 30 most recent days. If you used, the set the starting point of the results further back. Use integers.
@RELEASE_NAME@ - Name of the Release from the Release Module, for Outstanding Defects it looks at Target Release, for Total Defects Submitted it looks at Detected in Release. This was my best guess.
@OUTSTANDING_STATUSES@ - statuses that are outstanding i.e. 'New', 'Open, ...

I used the format of the QUERY PARAMETERS that Excel Reports uses in its queries [to be specific I'm using QC 9.2]. You can replace them with actual values that you want to use, or create the parameters after you paste the code in the excel report.

At the beginning, you can just delete the @OFFSET@, and it will give the last 30 days of data.

For @RELEASE_NAME@, you would supply the Release name, as it is in the Release module. If you are not using the Release module [I'm not myself, though I would like to be] the excel report can be tweaked to pay attention to fields like BG_PROJECT].

For @OUTSTANDING_STATUSES@, just replace that text with a list of Statuses that you consider not closed:
'New', 'Open', 'Ready To Test' etc...
Occasional Visitor
LukeRamage
Posts: 2
Registered: ‎07-05-2011
Message 10 of 11 (6,543 Views)

Re: Good sql queries using Excel Reports

Wondering if anybody has had a chance to migrate this query from Oracle to SQL. This would be very very very helpful.

 

Occasional Advisor
DipankarB
Posts: 10
Registered: ‎07-13-2011
Message 11 of 11 (6,541 Views)

Re: Good sql queries using Excel Reports

Thanks for your post ! We are also in QC 9.2 and migrating to QC 11, we are facing some errors in Excel reports after migrating projects to QC 11. The query uses parameters and works seamlessly in QC 9.2. Any help you provide will be appreciated. Link to the issue: http://h30499.www3.hp.com/t5/ITRC-Quality-Center-Forum/Excel-Report-parameter-binding-error-in-QC-11...
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.