How to build a SQL query to get the record of test cases which are in Failed or Passed per day and their total?

  • Questions
  • How to build a SQL query to get the record of test cases which are in Failed or Passed per day and their total?
Question ID: 107312
0
0

Hi Professionals,
I have a rquest to build a SQL Query which will pull all test cases which are in Faile dor Passed status per Tester and Per day and also need a total of test cases.

Please see the detail for detail.

I would like the report in the format that is attached.

Please help. ![alt text][1]

[1]: /storage/temp/519-tester.png

Marked as spam
Posted by (Questions: 30, Answers: 35)
Asked on December 14, 2016 8:30 pm
13 views
Answers (3)
0
Private answer

Can someone please help me?

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on January 3, 2017 8:02 pm
0
Private answer

Hey @miraclefren2,

The below SQL statement will get you what you need. However, It will be very taxing on your database resources.

; WITH temp AS
(SELECT DISTINCT (RN_TESTER_NAME) As 'Tester',
(SELECT COUNT(*)
FROM td.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '01/08/2016') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date1',
(SELECT COUNT(*)
FROM td.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '01/09/2016') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date2',
(SELECT COUNT(*)
FROM td.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '01/10/2016') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date3'
FROM td.RUN rn)

SELECT Tester As 'Tester Name',
Date1 As '01/08/2016',
Date2 As '01/09/2016',
Date3 As '01/10/2016',
Date1 + Date2 + Date3 As 'Total'
FROM temp

Since all of the data comes from a single table (Run table), I would instead recommend a simpler statement to pull only the rows and columns that you need and using a spreadsheet to provide the logic for the filters and counts. It may take a bit longer to set up initially, but won't crash your database or cause others to slow down while your report runs.

SELECT RN_TESTER_NAME, RN_EXECUTION_DATE, RN_STATUS

FROM td.RUN

WHERE RN_EXECUTION_DATE BETWEEN '2016/01/08' AND '2016/01/13' AND
RN_STATUS IN ('Passed', 'Failed')

Marked as spam
Posted by (Questions: 3, Answers: 168)
Answered on December 15, 2016 3:12 pm
However, I am having issue while running the 1st block of sql which you have on the top. I copied teh same and try to ran in my Oracle SQL and got the error below. ![alt text][1] Please help. I'm not sure where is my issue is. [1]: /storage/temp/521-error.png
( at December 16, 2016 4:18 am)
0
Private answer

I copied the below code from your 1st block and formated as per my database name and date fromat as Im doing in Oracle database. But I am having issue as attached in screenshot. Please help. ![alt text][1]

; WITH temp AS
(SELECT DISTINCT (RN_TESTER_NAME) As 'Tester',
(SELECT COUNT(*)
FROM PO_D02_DB.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '01-DEC-2015') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date1',
(SELECT COUNT(*)
FROM PO_D02_DB.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '02-DEC-2015') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date2',
(SELECT COUNT(*)
FROM PO_D02_DB.RUN
WHERE RN_TESTER_NAME = rn.RN_TESTER_NAME AND
datediff(day, RN_EXECUTION_DATE, '03-DEC-2015') = 0 AND
RN_STATUS IN ('Passed', 'Failed')) As 'Date3'
FROM PO_D02_DB.RUN rn)

SELECT Tester As 'Tester Name',
Date1 As '01/08/2016',
Date2 As '01/09/2016',
Date3 As '01/10/2016',
Date1 + Date2 + Date3 As 'Total'
FROM temp

[1]: /storage/temp/523-error.png

Marked as spam
Posted by (Questions: 30, Answers: 35)
Answered on December 16, 2016 4:42 am
Hi @Jafar, Would you please help me on the above issue? Thanks.
( at December 19, 2016 3:49 pm)