Changes to the community structure and the menu bar
Significant changes have happened to the structure of our community and our drop down menu bar. READ ABOUT IT HERE
Quality Center / ALM User Discussions
cancel

Need Help in merging the two SQL query

Highlighted
NDEY Regular Contributor.
Regular Contributor.

Need Help in merging the two SQL query

I have two different query in HP ALM, but i want to merge and get it into one. I am not that good in SQL query so I am facing hard time in merging the query.

Query 1: Getting the execution Count for the tester

Select
TESTCYCL.TC_ACTUAL_TESTER as 'Tester',
sum(case when TC_Status In('Blocked','Passed','Failed','Not Completed') then 1 else 0 end) as 'Total',
sum(case when TC_Status = 'Passed' then 1 else 0 end) as 'Pass',
sum(case when TC_Status = 'Failed' then 1 else 0 end) as 'Fail',
sum(case when TC_Status = 'Blocked' then 1 else 0 end) as 'Blocked',
sum(case when TC_Status In('Not Completed','Defferred','N/A') then 1 else 0 end) as 'Others'

From TESTCYCL
Where
TESTCYCL.TC_EXEC_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
And
TESTCYCL.TC_ACTUAL_TESTER in ('Username1')
Group by TC_ACTUAL_TESTER

 

 

 

Query 2: Getting the Defect raise by the tester

SELECT
BG_DETECTED_BY,
Sum(case when BG_Status Not in ('Closed','Defect Resolved','Rejected')then 1 else 0 end) as 'Defect Raised'
FROM BUG
Where BUG.BG_DETECTED_BY in ('username1')
AND BUG.BG_DETECTION_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
Group by BG_DETECTED_BY

 

I have tried inner join/ Left Join but the count of the defect raised by the user is not matching

Query3 : That i have tried:

Select
TESTCYCL.TC_ACTUAL_TESTER as 'Tester',
sum(case when TC_Status In('Blocked','Passed','Failed','Not Completed') then 1 else 0 end) as 'Total',
sum(case when TC_Status = 'Passed' then 1 else 0 end) as 'Pass',
sum(case when TC_Status = 'Failed' then 1 else 0 end) as 'Fail',
sum(case when TC_Status = 'Blocked' then 1 else 0 end) as 'Blocked',
sum(case when TC_Status In('Not Completed','Defferred','N/A') then 1 else 0 end) as 'Others',
Sum(case when BG_Status Not in ('Closed','Defect Resolved','Rejected')then 1 else 0 end) as 'Defect Raised'

From (TESTCYCL Left Join BUG on TESTCYCL.TC_ACTUAL_TESTER =
BUG.BG_DETECTED_BY AND BUG.BG_DETECTION_DATE = CAST(CURRENT_TIMESTAMP AS DATE))
Where
TESTCYCL.TC_EXEC_DATE = CAST(CURRENT_TIMESTAMP AS DATE)
And
TESTCYCL.TC_ACTUAL_TESTER in ('username1')
Group by TC_ACTUAL_TESTER

Top Contributors Last 30 Days