Quality Center / ALM Practitioners Forum
cancel

Need Help in merging the two SQL query

Highlighted
NDEY
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