Subquery Needed? - 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

Subquery Needed?

Author

Topic

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 17:29:54

The below query returns (amongst other things):

Number of Test Instances is 3
Number of Steps is 8

This is correct EXCEPT Number of Steps should be 5, not 8.

To illustrate why:

TestInstance1 has three steps that can be run
TestInstance2 has one step that can be run
TestInstance3 has one step that can be run
TOTAL STEPS = 5

The reason I'm getting 8 instead of 5 is, the steps are associated with a "Run" of a the test instance.

TestInstance1 was run twice so its three steps were executed twice: 3 steps * 2 executions = 6 steps.

TestInstance2 was run once so its one step was executed once: 1 step * 1 execution = 1 step.

TestInstance3 was run once so its one step was executed once: 1 step * 1 execution = 1 step.

6 + 1 + 1 = 8 Steps

HOWEVER ...

I ONLY want to count steps for the most recent run of the three test instances. So, I want 3 + 1 + 1 = 5. The way to tell is to use RN_EXECUTION_DATE and RN_EXECUTION_TIME to determine the last run of a test instance and use only that last run in counting steps.

Here's the SQL. I can't figure out how to count only steps in the last run of each test instance:



-- Test Set Report

SELECT

-- Test Set Fields
CYCLE.CY_CYCLE AS "Test Set",
--QC Memo fields must be converted to nvarchar
convert(nvarchar(MAX),CYCLE.CY_COMMENT) AS "Description",
CYCLE.CY_USER_01 AS "Functional Area",
CYCLE.CY_OPEN_DATE AS "Open Date",
CYCLE.CY_CLOSE_DATE AS "Close Date",
CYCLE.CY_REQUEST_ID AS "ITG Request ID",
CYCLE.CY_STATUS AS "Status",
CYCLE.CY_PINNED_BASELINE AS "Baseline",
CYCLE.CY_CYCLE_ID AS "Cycle ID",
CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",
CYCLE.CY_VTS AS "Modified",

--Run fields needed to get only latest Run Steps
--MAX(RUN.RN_EXECUTION_DATE) AS 'LASTRUNDATE',
--MAX(RUN.RN_EXECUTION_TIME) AS 'LASTRUNTIME',

-- Test Instance Fields & Step Fields
COUNT(DISTINCT TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",
COUNT(STEP.ST_ID) AS "Number of Steps"

FROM TESTCYCL

INNER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
INNER JOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID
INNER JOIN STEP ON ST_RUN_ID = RN_RUN_ID

GROUP BY CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID,
CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS

ORDER BY CYCLE.CY_CYCLE_ID

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-11-13 : 18:00:32

You need to do something like this:
add (select RN_TESTCYCL_ID, max(RUN.RN_EXECUTION_DATE) from run) as an inline view
and join to that into to your main query.
Unfortunately you have made life hard for yourself by splitting date/time out so you'll have to work out how to reconstitute those 2 columns into something usable.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-13 : 18:41:44

Thanks, I'll look into what you suggest and FYI, HP Software has made life hard for me .. this is the DB schema behind HP Quality Center.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-14 : 08:14:14

I'm very close. Below is the current state of my query. What has changed is, I added a sub-query using RN_EXECUTION_TIME:

(SELECT MAX(RUN.RN_EXECUTION_TIME) FROM RUN),

Now I get a row for every test instance whereas I still only want one row where the counts are rolled up. See original post above for details.

The below query returns these rows (I'm only showing rows for one particular test set - the query returns rows for others):

Row 1:

Number of Test Instances = 1
Number of Steps = 1
Number of Defects = 0

Row 2:

Number of Test Instances = 1
Number of Steps = 3
Number of Defects = 1

Row 3:

Number of Test Instances = 1
Number of Steps = 1
Number of Defects = 0

Row 4:

Number of Test Instances = 1
Number of Steps = 3
Number of Defects = 1

What I want is one row that is a rollup of rows 1, 2 and 3. Row 4 is the same test instance as row 1 but was run earlier (RN_EXECUTION_TIME). Again, I only want the most recent run execution of a test instance.

Here's what the data I want should look like:

Row 1:

Number of Test Instances = 3
Number of Steps = 5
Number of Defects = 1


-- Test Set Report

SELECT

-- Test Set Fields
CYCLE.CY_CYCLE AS "Test Set",
CONVERT(NVARCHAR(MAX),CYCLE.CY_COMMENT) AS "Description", --QC Memo fields must be converted to nvarchar
CYCLE.CY_USER_01 AS "Functional Area",
CYCLE.CY_OPEN_DATE AS "Open Date",
CYCLE.CY_CLOSE_DATE AS "Close Date",
CYCLE.CY_REQUEST_ID AS "ITG Request ID",
CYCLE.CY_STATUS AS "Status",
CYCLE.CY_PINNED_BASELINE AS "Baseline",
CYCLE.CY_CYCLE_ID AS "Cycle ID",
CYCLE.CY_ASSIGN_RCYC AS "Target Cycle",
CYCLE.CY_VTS AS "Modified",

-- Test Instance, Step, and Step Linked Defect Counts
COUNT(DISTINCT TESTCYCL.TC_TEST_ID) AS "Number of Test Instances",
COUNT(STEP.ST_ID) AS "Number of Steps",
COUNT(LT.LN_BUG_ID) AS "Number of Defects",

-- Only the most recent execution (run) of a test instance should be counted
(SELECT MAX(RUN.RN_EXECUTION_TIME) FROM RUN),

-- Test Set Full Folder Path (always last)
CONVERT(NVARCHAR(MAX),CYCLE.CY_USER_07) AS "Full Folder Path"

FROM TESTCYCL

INNER JOIN CYCLE ON CYCLE.CY_CYCLE_ID = TESTCYCL.TC_CYCLE_ID
INNER JOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID
INNER JOIN STEP ON ST_RUN_ID = RN_RUN_ID
LEFT JOIN LINK AS LT ON LN_ENTITY_ID = ST_ID AND LN_ENTITY_TYPE = 'STEP'

GROUP BY CYCLE.CY_CYCLE, CAST (CYCLE.CY_COMMENT AS NVARCHAR(MAX)), --QC Memo fields must be converted to nvarchar
CYCLE.CY_USER_01, CYCLE.CY_OPEN_DATE, CYCLE.CY_CLOSE_DATE, CYCLE.CY_REQUEST_ID,
CYCLE.CY_STATUS, CYCLE.CY_PINNED_BASELINE, CYCLE.CY_CYCLE_ID, CYCLE.CY_ASSIGN_RCYC, CYCLE.CY_VTS,
CAST (CYCLE.CY_USER_07 AS NVARCHAR(MAX)) --QC Memo fields must be converted to nvarchar
, RUN.RN_EXECUTION_TIME

ORDER BY CYCLE.CY_CYCLE_ID

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2012-11-14 : 08:34:20

No you, can't just select max dates. You need to select the ID & Max as I described and join to it:

select
....all your stuff....,
from
...all your tables etc.....
INNER JOIN
(select RN_TESTCYCL_ID, max(RUN.RN_EXECUTION_DATE) oldestExecDate from run) OldestExecDates
ON (RUN.RN_TESTCYCL_ID = OldestExecDates.RN_TESTCYCL_ID and ...date joins on RUN....)
....

So you need an inline view to select each highest date/time for each ID then join back to your main select.
I'm not going to do it for you as I am only an end user of HP CQ, so I don't know the details and I'm not about to learn it. Not sure which is worse - using the thing or coding from it.

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-14 : 08:51:30

First, thanks for your help. Also, I'm sorry to have irritated you. I spent about 5 hours trying to make this work using your suggestion which undoubtedly, you know what you're talking about.

I really wasn't asking that you "do it for me". I want to learn this, not copy code someone else writes - no one can learn that way.

I am posting under the "New to SQL Server Programming" so sometimes I don't quite understand the advice I get but I try to and I spend a lot of time trying to implement. If I come back with the wrong answer, it doesn't mean I wasn't paying attention. I can't document and no one wants to read everything I tried with no success.

So, once again - I apologize for not "getting it" ... I really am trying. I will continue to try. Forgive me if I ask more questions.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 09:01:49

Try replacing
INNER JOIN RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID

with
INNER JOIN
(SELECT RN_Run_ID,RN_Testcycl_ID
,ROW_NUMBER() OVER(PARTITION BY RN_Testcycl_id ORDER BY RN_Execution_date DESC,RN_Execution_Time desc ) as lastRun
FROM RUN
) RUN

and then add a WHERE clause after your joins and before the GROUP BY

WHERE run.LastRun = 1

Jim

P.S.You should consider aliasing your tables, it makes your code much easier to read and maintain.

Everyday I learn something that somebody else already knew

planetoneautomation
Posting Yak Master

105 Posts

Posted - 2012-11-14 : 10:41:02

Thank you Jim. I had to add the "ON" clause but when I did, this got what I wanted. I wasn't aware of the ROW_NUMBER() OVER (PARTION function until your post - I think that is a much better approach.

INNER JOIN
(
SELECT
  RN_RUN_ID, RN_TESTCYCL_ID, ROW_NUMBER()
  OVER(PARTITION BY RN_TESTCYCL_ID ORDER BY RN_EXECUTION_DATE DESC,RN_EXECUTION_TIME DESC) AS LASTRUN
  FROM RUN
) RUN ON RN_TESTCYCL_ID = TC_TESTCYCL_ID
INNER JOIN STEP ON ST_RUN_ID = RN_RUN_ID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources