HP QC-ALM Reporting Queries

Duplicate_TestScripts_TestLab

select rcyc_id as cycle_id, cycle_name,
cy_cycle_id as test_set_id, cy_cycle as test_set_name, test_id, ts_name
from testcycl t
join cycle on cy_cycle_id = tc_cycle_id
join (
select rcyc_id, rcyc_name as cycle_name, tc_test_id as test_id
from release_cycles
join testcycl on tc_assign_rcyc = rcyc_id
-- where rcyc_name = 'MY CYCLE NAME'
group by rcyc_id, rcyc_name, tc_assign_rcyc, tc_test_id
having count(tc_test_id) > 1
) multi on test_id = t.tc_test_id and tc_assign_rcyc = rcyc_id
join test on ts_test_id = t.tc_test_id
order by 2, 6, 5, 4


Number of Test Steps Count for not passed status.

SELECT CY_CYCLE AS "Test Set",
       TS_NAME AS "Test Script Name",
       TS_STEPS as "Test Steps", /*Test.Steps*/
       TC_ACTUAL_TESTER AS "Tester",
       TC_STATUS AS "Execution Status",
LEFT(CONVERT(VARCHAR,TC_EXEC_DATE,20),10)  AS "Execution Date"

FROM TEST, TESTCYCL, CYCLE
WHERE CY_CYCLE_ID = TC_CYCLE_ID
AND   TS_TEST_ID = TC_TEST_ID
AND TC_STATUS <> 'Passed'
AND CY_CYCLE_ID IN ( cycleid)

Number of Test Steps Count for passed status.
By this, calculate the Average number of test steps can be executed per day.

SELECT CY_CYCLE AS "Test Set",
       TS_NAME AS "Test Script Name",
       TS_STEPS as "Test Steps", /*Test.Steps*/
       TC_ACTUAL_TESTER AS "Tester",
       TC_STATUS AS "Execution Status",
LEFT(CONVERT(VARCHAR,TC_EXEC_DATE,20),10)  AS "Execution Date"

FROM TEST, TESTCYCL, CYCLE
WHERE CY_CYCLE_ID = TC_CYCLE_ID
AND   TS_TEST_ID = TC_TEST_ID
AND TC_STATUS = 'Passed'
AND CY_CYCLE_ID IN ( cycleid)

Defects are in Closed Status but the test scripts/cases were in not passed status.
SELECT  t.LN_BUG_ID as "Defect ID",
        BUG.BG_USER_03 AS "Application",
        BUG.BG_PRIORITY AS "Priority",
        t.BlockedScripts AS "No. Of Blocked Test Cases",
        BUG.BG_USER_06 AS "Estimated Fix Date",
        BUG.BG_DETECTION_DATE AS "Opened Date",
        BUG.BG_STATUS AS "Current Status",
        BUG.BG_RESPONSIBLE AS "Assigned To",
        BUG.BG_SUMMARY AS "Summary",
        BUG.BG_USER_05 As "Process Stream"
        from bug, (
SELECT LN_BUG_ID,
COUNT(LN_ENTITY_ID) AS 'BlockedScripts'
FROM LINK
WHERE LN_ENTITY_TYPE = 'TESTCYCL'
AND  LN_BUG_ID in (
   SELECT BG_BUG_ID
   FROM BUG
   WHERE BG_STATUS = 'Closed'
  AND BUG.BG_DETECTED_IN_RCYC = 1028
/*AND BUG.BG_USER_01 = 'SIT'  */
AND BUG.BG_USER_02 = 'SIT')
GROUP BY LN_BUG_ID
HAVING COUNT(LN_ENTITY_ID) > 1) as t
where t.LN_BUG_ID = bug.bg_bug_id
order by 4 desc

Defects are in Closed Status but the test scripts/cases were in blocked status.

SELECT LN_BUG_ID as "Defect ID",
COUNT(LN_ENTITY_ID) AS '# BLOCKED SCRIPTS'
FROM LINK
WHERE LN_ENTITY_TYPE = 'TESTCYCL'
AND  LN_BUG_ID in (
   SELECT BG_BUG_ID
   FROM BUG
 WHERE BG_STATUS = 'Closed'
 --WHERE BG_STATUS <> 'Closed'
 /*Defect.Detected in Cycle  and  BUG.BG_DETECTED_IN_RCYC = 'SIT 2015 Cycle 1')*/
   AND BG_USER_02 = 'SIT'
    and LN_BUG_ID >3310)

GROUP BY LN_BUG_ID
HAVING COUNT(LN_ENTITY_ID) >= 1
ORDER BY 2 DESC

Defects which are failed in retest will be moved into Reopen status. Those defects needs to be tracked to provide the root cause analysis.
SELECT
BUG.BG_BUG_ID as Defect_ID,
BUG.BG_STATUS as Status,
BUG.BG_SUMMARY as Summary,
/*AUDIT_PROPERTIES.AP_FIELD_NAME,*/
AUDIT_PROPERTIES.AP_OLD_VALUE,
AUDIT_PROPERTIES.AP_NEW_VALUE
FROM BUG, AUDIT_LOG, AUDIT_PROPERTIES
WHERE AUDIT_LOG.AU_ENTITY_ID = BUG.BG_BUG_ID and AUDIT_LOG.AU_ENTITY_TYPE = 'BUG' and AUDIT_PROPERTIES.AP_ACTION_ID = AUDIT_LOG.AU_ACTION_ID and AUDIT_PROPERTIES.AP_FIELD_NAME = 'BG_STATUS' and AUDIT_PROPERTIES.AP_OLD_VALUE = 'Retest Failed'  and BUG.BG_BUG_ID > X /*(Defect id is greater than X)*/


Comments

Post a Comment

Popular posts from this blog

Terminal Emulation – QTP Automation Testing

Terminal Emulation – QTP Automation Testing Topics to be covered 1. Basic understanding of the terminal emulator 2. Emulation terminology 3. Configuring the QTP to work with TE application 4. Automation testing methodology Basics of Emulator • Duplicates the functions of one system using a different system, so that second system behaves like a first system. • Computer simulation Example: AS400 and mainframes is a dark green color screen AS400 is introduced in the year of 1988 by IBM, formally renamed as IBM iseries but still called as AS400 is mid range server for the small business and large enterprises. It will work good within distributed network and web based applications for execution it uses OS400 ie operating systems. TN5250 Terminal emulation protocol IBM owned iseries access by TN5250 with WRQ’s reflection, humming bird’s host explorer, Cybele software’s Z scope Express 5250. AS400 is also a Mainframe which are used for large organizations, for critical applic...
Read more

HP QC SQL Queries

Test-lab: SELECT CY_CYCLE AS "Test Set",        TS_NAME AS "Test Script Name",        TC_ACTUAL_TESTER AS "Tester",        TC_STATUS AS "Execution Status",        LEFT(CONVERT(VARCHAR,TC_EXEC_DATE,20),10)  AS "Execution Date" FROM TEST, TESTCYCL, CYCLE WHERE CY_CYCLE_ID = TC_CYCLE_ID AND   TS_TEST_ID = TC_TEST_ID AND CY_CYCLE_ID IN (cycleid) Test-Summary: SELECT CY_CYCLE,        TC_STATUS,        COUNT(TS_NAME) FROM TEST, TESTCYCL, CYCLE WHERE CY_CYCLE_ID = TC_CYCLE_ID AND   TS_TEST_ID = TC_TEST_ID AND CY_CYCLE_ID IN (cycleid) GROUP BY CY_CYCLE, TC_STATUS ORDER BY CY_CYCLE, TC_STATUS Run-rate: SELECT CY_CYCLE,        TC_ACTUAL_TESTER,        TC_STATUS,        L...
Read more

Challenges while executing the test cases for large project

Defects are in Closed status but the test scripts in either Not Completed or Failed or Blocked Status. Based on the conversation with Program Director regarding this, we have followed this method to get more productivity Identify the defects which are closed and find the linked test scripts. Moved those to No Run status by updating the comment section in the test plan>>Run suite. Identified the test scripts which are in the Not completed status for more than two or three days Check the test steps status and if there is an update on those ie more than 50% of the test steps has been executed in that case need to check with the concerned tester for further update. Else move the test script to no run status. Possibility of no action on the test scripts due to concern tester is moved out of the portfolio or left the company or not feeling well or any emergency Defect is in "closed" status, so the test script is moved to "No Run" status t...
Read more