Can this even be done in SQL? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Can this even be done in SQL?

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-15 : 08:52:22

I know I can accomplish this writing vb script and using simple SQL selects, storing data and manipulating with logic as necessary.

Ideally though, because to do it with a vb script would be doing it outside of the application rather than inside the application with its "SQL Reports" capability, I'd rather do it in SQL if possible.

So the question is, can this even be done in a single SQL query? Here is the pseudo code:

1. Select CYCLE.CY_CYCLE_ID from CYCLE where CYCLE.CY_CYCLE = known value (provided by user) ... there may be multiple rows returned and each row must be processed with the following ....
2. Select TESTCYCL.TC_TEST_ID from TESTCYCL where CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
3. For each TESTCYCL.TC_TEST_ID:
If TESTCYCL.TC_STATUS = "No Run" get TEST.TS_NUM_STEPS from the TEST table where TESTCYCL.TC_TEST_ID = TEST.TS_TESTID and accumulate for reporting
If TESTCYCL.TC_STATUS = "Passed" get TEST.TS_STEPS from the TEST table where where TESTCYCL.TC_TEST_ID = TEST.TS_TESTID and accumulate for reporting
If the status is "Not Completed" or "Failed", get the RUN.RN_RUN_ID from the RUN table where CYCLE.CY_CYCLE_ID = RUN.RN_CYCLE_ID ... Since there are multiple RUN.RN_ID's returned, only get the *first* run id based by sorting DESC on RUN.RN_RUN_NAME
and then select each row from the STEP table where STEP.ST_RUN_ID = RUN.RN_RUN_ID and count the rows that have STEP.ST_STATUS = "No Run", "Passed", "Failed", "N/A" and "Not Completed" (accumulate each separately for reporting.

What the result should look like is this:

[Code]
Test Set %Passed %Failed %N/A %No Run %Not Completed

ABC 10 20 5 10 55
XYZ 65 5 5 20 5
...
[/CODE]

I am fairly new to SQL programming and I'm guessing the above is either not possible or is very, very complex.

Any ideas / thoughts?

Kristen
Test

22859 Posts

Posted - 2010-02-15 : 09:07:05

Please reformat so we don't have to scroll sideways to try to read your question

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 09:09:17

some questions

1. Can you tell how tables involved are related i.e is it 1-1 or 1-many etc?
2. Also what does Test Set field in your output designate? i cant see such a field in explanation
3. You've two types of count coming for No Run,Passed (from TEST table and from STEP table). which of one is shown in output? or does that involve some kind of calculation b/w them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-15 : 09:38:17

"Test Set" = CYCLE.CY_CYCLE

1. There are many CYCLE.CY_CYCLE rows - the report requires processing each row selected
1. There is one TESTCYCL row for each CYCLE.CY_CYCLE
2. There are many TEST rows for each TESTCYCL row
3. There are many STEPS for each TEST (or NO steps depending on status - see CAVEAT below)
4. CAVEAT: For a TEST with TESTCYCL.TC_STATUS = "No Run", there are no rows in STEP ... this means all steps in the test were not executed and therefore no rows in STEP table ... however, the total number of rows for the TEST can be pulled from TEST.TS_NUM_STEPS ... this number of steps from TEST are counted AS IF they were rows in STEP
5. CAVEAT: For a TEST with TESTCYCL.TC_STATUS = "Passed", I could count rows in STEP just like I do the other statuses but since for the the TEST status to be Passed, all rows must be Passed I can just get number of steps from TEST table just like #4 above.

The requirement for the report is, for each cycle (test set), report the percentage of steps that Passed, Failed, Not Completed, No Run or N/A - this is cummulative across all tests.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 09:48:22

does that mean For a TEST with TESTCYCL.TC_STATUS = "Passed", you will have only one record in STEP with ST_STATUS = "Passed" and similarly for other statuses as well?

Also you say you've many TEST rows for each TESTCYCL row so are we looking at aggregate across all of them for a CYCLE.CY_CYCLE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-15 : 10:17:33

Let me give a simplified example from a user perspective of the business process that creates the data:

TestSet1 contains three tests - Test1, Test2, Test3

Test1 has 3 steps - Step1, Step2, Step3
Test2 has 3 steps - Step1, Step2, Step3
Test3 has 3 steps - Step1, Step2, Step3

Therefore, there are 9 steps total in this test set

When a tester executes TestSet1, he/she "runs" each test which means he/she executes each step in each test.

Let's say the test results are as follows:

Test1: Passed (means all steps must have passed)
Test2: Failed (means at least one step failed - other steps can be any status)
Test3: No Run (means no steps have been run yet)

Let's say the results of each *step* within each test is as follows:

Test1 - all three step have a status of Passed (this has to be since the status of the test itself is Passed)
Test2 - step 1 Passed, step 2 Failed, step 3 was Not Completed
Test3 - no steps were executed therefore there are no statuses for each step - so the test itself gets a status of No Run

Step statuses by Test:

Test1 - 3 Passed, 0 Failed, 0 No Run, 0 Not Completed, 0 N/A
Test2 - 1 Passed, 2 Failed, 0 No Run, 0 Not Completed, 0 N/A
Test3 - there are no steps status since this test was not run - this means there were 3 No Run steps

So the report looks like this:


TestSetName     %Passed     %Failed     %No Run     %Not Completed     %N/A

TestSet1        44.4        11.1        33.3        11.1               0

Does this help?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 11:24:05

something like this


SELECT CYCLE.CY_CYCLE,
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
     THEN TEST.TS_NUM_STEPS
     ELSE COALESCE(rn.NR,0)
    END) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
         THEN TEST.TS_NUM_STEPS
         WHEN TESTCYCL.TC_STATUS = 'Passed'
         THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE(rn.NC,0) + COALESCE(rn.NA,0)
     ELSE rn.Total
    END) AS [%No Run],
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'Passed'
     THEN TEST.TS_STEPS
     ELSE COALESCE(rn.P,0)
    END) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
         THEN TEST.TS_NUM_STEPS
         WHEN TESTCYCL.TC_STATUS = 'Passed'
         THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE(rn.NC,0) + COALESCE(rn.NA,0)
     ELSE rn.Total
    END) AS [%Passed],
SUM(COALESCE(rn.F,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
         THEN TEST.TS_NUM_STEPS
         WHEN TESTCYCL.TC_STATUS = 'Passed'
         THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE(rn.NC,0) + COALESCE(rn.NA,0)
     ELSE rn.Total
    END) AS [%Failed],
SUM( COALESCE(rn.NA,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
         THEN TEST.TS_NUM_STEPS
         WHEN TESTCYCL.TC_STATUS = 'Passed'
         THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE(rn.NC,0) + COALESCE(rn.NA,0)
     ELSE rn.Total
    END) AS [%N/A],
SUM(COALESCE(rn.NC,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run'
         THEN TEST.TS_NUM_STEPS
         WHEN TESTCYCL.TC_STATUS = 'Passed'
         THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE(rn.NC,0) + COALESCE(rn.NA,0)
     ELSE rn.Total
    END) AS [%Not Completed]
from CYCLE 
JOIN TESTCYCL 
ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
INNER JOIN TEST
ON TESTCYCL.TC_TEST_ID = TEST.TS_TESTID
LEFT JOIN
(SELECT RUN.RN_CYCLE_ID,
 COUNT(CASE WHEN STEP.ST_STATUS ='Not Run' THEN 1 ELSE NULL END) AS [NR],
COUNT(CASE WHEN STEP.ST_STATUS ='Passed' THEN 1 ELSE NULL END) AS [P],
COUNT(CASE WHEN STEP.ST_STATUS ='Failed' THEN 1 ELSE NULL END) AS [F],
COUNT(CASE WHEN STEP.ST_STATUS ='N/A' THEN 1 ELSE NULL END) AS [NA],
COUNT(CASE WHEN STEP.ST_STATUS ='Not Completed' THEN 1 ELSE NULL END) AS [NC],
COUNT(*) AS Total
  FROM RUN
 JOIN (SELECT RN_CYCLE_ID,MAX(RN_RUN_NAME) AS Recent
       FROM RUN
       GROUP BY RN_CYCLE_ID) r1
ON  r1.RN_CYCLE_ID= RUN.RN_CYCLE_ID
AND r1.Recent = RUN.RN_RUN_NAME
JOIN STEP
ON STEP.ST_RUN_ID = RUN.RN_RUN_ID
GROUP BY RUN.RN_CYCLE_ID
)rn
ON rn.RN_CYCLE_ID = CYCLE.RN_CYCLE_ID
GROUP BY CYCLE.CY_CYCLE

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-15 : 12:36:02

Wow - with a few tweeks (I got a column name wrong, for example) this ran and produced output that I think is very close. I say very close because the percentages don't add up to 100 (or even close to 100 - sometimes 70 to 80) ... I will try to figure that out myself. But the bottom line is, it apparently can be done albeit with SQL knowledge and experience much greater than mine.

Here is the code that I tweeked and ran which produces output that is less than 100% for each row ... (note I had to scrunch some code into fewer lines because in debugging, the message box showing errors was so long (because it showed the entire SQL followed by the error) that it when below the bottom of the screen and I couldn't read the error)

SELECT CYCLE.CY_CYCLE,
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS ELSE COALESCE(rn.NR,0) END) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE
(rn.NC,0) + COALESCE(rn.NA,0) ELSE rn.Total END) AS [%No Run],
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS ELSE COALESCE(rn.P,0) END) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE
(rn.NC,0) + COALESCE(rn.NA,0)ELSE rn.Total END) AS [%Passed],
SUM(COALESCE(rn.F,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE
(rn.NC,0) + COALESCE(rn.NA,0) ELSE rn.Total END) AS [%Failed],
SUM( COALESCE(rn.NA,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE
(rn.NC,0) + COALESCE(rn.NA,0) ELSE rn.Total END) AS [%N/A],
SUM(COALESCE(rn.NC,0)) *100.0/
SUM(CASE WHEN TESTCYCL.TC_STATUS = 'No Run' THEN TEST.TS_STEPS WHEN TESTCYCL.TC_STATUS = 'Passed' THEN TEST.TS_STEPS + COALESCE(rn.F,0) + COALESCE
(rn.NC,0) + COALESCE(rn.NA,0) ELSE rn.Total END) AS [%Not Completed]
from CYCLE
JOIN TESTCYCL ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
INNER JOIN TEST ON TESTCYCL.TC_TEST_ID = TEST.TS_TEST_ID
LEFT JOIN (SELECT RUN.RN_CYCLE_ID,
COUNT(CASE WHEN STEP.ST_STATUS ='Not Run' THEN 1 ELSE NULL END) AS [NR],
COUNT(CASE WHEN STEP.ST_STATUS ='Passed' THEN 1 ELSE NULL END) AS [P],
COUNT(CASE WHEN STEP.ST_STATUS ='Failed' THEN 1 ELSE NULL END) AS [F],
COUNT(CASE WHEN STEP.ST_STATUS ='N/A' THEN 1 ELSE NULL END) AS [NA],
COUNT(CASE WHEN STEP.ST_STATUS ='Not Completed' THEN 1 ELSE NULL END) AS [NC],
COUNT(*) AS Total
FROM RUN
JOIN (SELECT RUN.RN_CYCLE_ID,MAX(RUN.RN_RUN_NAME) AS Recent
FROM RUN
GROUP BY RUN.RN_CYCLE_ID) r1
ON r1.RN_CYCLE_ID = RUN.RN_CYCLE_ID
AND r1.Recent = RUN.RN_RUN_NAME
JOIN STEP
ON STEP.ST_RUN_ID = RUN.RN_RUN_ID
GROUP BY RUN.RN_CYCLE_ID
)rn
ON rn.RN_CYCLE_ID = CYCLE.CY_CYCLE_ID
GROUP BY CYCLE.CY_CYCLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-15 : 12:41:24

if you could give some sample data in below format i can try to fix it

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

particularly i want to know under what all conditions you will have records in which all tables

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2010-02-15 : 14:55:23

I looked at the link for posting sample data but I really don't understand it ... maybe this will help ... this follows the example I put forth earlier where there were 3 tests in a cycle (test set) with different statuses ...

CYCLE table: CYCLE.CY_CYCLE_ID = 8, CYCLE.CY_CYCLE = "My Test Set name" (one row)
TESTCYCL table: TESTCYCL.TC_CYCLE_ID = 8, TC_TEST_ID = 52 (one row)
TESTCYCL.TC_CYCLE_ID = 8, TC_TEST_ID = 53 (one row)
TESTCYCL.TC_CYCLE_ID = 8, TC_TEST_ID = 54 (one row)
TEST table: TEST.TS_ID = 52, TEST.TS_STEPS = 25, TEST.TS_STATUS = "Passed" (one row)
TEST.TS_ID = 53, TEST.TS_STEPS = 15, TEST.TS_STATUS = "Failed" (one row)
TEST.TS_ID = 54, TEST.TS_STEPS = 10, TEST.TS_STATUS = "No Run" (one row)
RUN table: RUN.RN_CYCLE_ID = 8, RUN.RN_RUN_ID = 234 (multiple rows as follows)
STEPS table: STEPS.ST_RUN_ID = 234, STEPS.ST_STATUS = "Passed" (four rows - 3 for Test1 and 1 for Test2)
STEPS.ST_RUN_ID = 234, STEPS.ST_STATUS = "Failed" (one row for Test2 associated via run id)
STEPS.ST_RUN_ID = 234, STEPS.ST_STATUS = "Not Completed" (one row for Test2 associated via run id)
NO ROWS FOR TEST3 since its status is "No Run"

** There will be no rows in the STEPS table for a Test that has a TEST.TS_STATUS = "No Run" so the number of steps
not run will come from the value of TEST.TS_STEPS instead. We can also use the value of TEST.TS_STEPS for Tests with
TEST.TS_STATUS = "Passed" since by definition a Test with a status of "Passed" means all it steps Passed (so we don't have to count them in the STEPS table).

** For Test3 there will be rows in the STEPS table to spin through. The STEPS table must be read where ST_RUN_ID = 234 and RN_TEST_ID = 53 - there will be 15 steps to look at and accumulate number of Passed, Failed, Not Completed, No Run and N/A for.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources