ID and Parent ID - Multiple queries that create Relationship

[tqeonline]

Hello All. I'm going to try and explain my issue and see if people can help.

Background knowledge: We have Projects, that have Iterations, that have Releases, that have User Stories (Requirements).

Projects > Iterations > Releases > Requirements

But the database doesn't see it like that. so i'm trying to come up with a way to have an ID and then a Parent ID so i can feed this data to a treeview and have it cascade.

So the ParentID of an Iteration would be the Project and ParentID of Releases would be the Iteration etc...

Here are the Project and Iteration queries. I have added a RowID but don't know where to go from here.

--Project Level Data
select [RowId] = CAST(ROW_NUMBER() OVER(ORDER BY MIN(r.Rel_Start_Date)) AS INT),
c.CY_User_05 as 'Project', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete'
FROM dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.cy_user_05

--Iteration Level Data
select [RowId] = CAST(ROW_NUMBER() OVER(ORDER BY MIN(r.Rel_Start_Date)) AS INT),
c.CY_USER_06 as 'Iteration', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete' 
from dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.CY_User_05, c.CY_USER_06

I need to know how to make the parent ID of the Projects equal 0 and the parent ID's of the Iterations equal their corresponding Project.

I have attached the data being returned from both of these queries.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

tge,
Gonna have to back up a second.

1.)If all this data is stored in the same table, the first thing you need to do is create views (or table functions) to separate your logic for retrieving that particular bit of data.

2.)You'll eat your headset* if you try to join data on a dynamically generated ROW Number. These tables need some sort of key to tie them together.

3.) Are you wanting 1 big query with a result set like:
Project Iteration Release Requirement
A A A A
A A A B
A B A A
A B B B
...


Need Detail,
Lodlaiden

*-"I've seen guys eat their own headsets trying to get a 12:00 flasher online..." <-You're working with that level of futility if you trying joining an basically random numbers


A lack of experience doesn't prevent you from doing a good job.

 

[tqeonline]

the data i want it to return would be like this:

1 0 Project
2 1 Iteration
3 2 Release
4 3 Requirement
5 3 Requirement

so how the iteration takes the id (1) of the project it is tied to.

I am still trying to figure it out but I may just run it through in code.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

Then you want table functions, that you can pass filtering criteria to and UNION after.

Something like this:

SELECT ROWNUMBER(), squished.* FROM(
SELECT * from dbo.udfGetProjects(@Project, @Iteration, @Release, @Requirement) as p UNION ALL
SELECT * from dbo.udfGetIterations(@Project, @Iteration, @Release, @Requirement) as i ) as squished

A lack of experience doesn't prevent you from doing a good job.

 

[tqeonline]

Ok - i think i have an idea on how to get it.

I unioned everything and i created two columns: Primary_Ident and Secondary_Ident. In the code behind of my project I will take the data and perform recursion to establish the ID and Parent_ID's

Project - Primary = 0, Secondary = ''
Iteration - Primary = Project Name, Secondary = ''
Releases - Primary = Project Name, Secondary = Iteration Name
Requirement - Primary = Release Name, Secondary = ''

It returns all the data correctly. Then what I would do in the code behind is loop through the rows adding an ID and then loop through again with this logic:

if Primary is not null and secondary is not null then
for x = 0 to rows.count - 1
'perform lookup
'update parent id
next x
end if
.
.

Here is the UGLY long query: where should I begin on consolodating it?

use SQA_bsd_qa_sandbox_db
--[RowId] = CAST(ROW_NUMBER() OVER(ORDER BY MIN(r.Rel_Start_Date)) AS INT),
--Project Level Data
select c.CY_User_05 as 'Name', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete',
'0' as Primary_Ident,
'' as Secondary_Ident
FROM dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.cy_user_05

union
--Iteration Level Data
select c.CY_USER_06 as 'Name', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete' ,
c.CY_USER_05 as Primary_Ident,
'' as Secondary_Ident
from dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.CY_User_05, c.CY_USER_06

union
--Release Level Data
select r.Rel_Name as 'Name', 
MIN(r.Rel_Start_Date) as 'Start Date', --change to REL_USER_02 on live DB
MAX(r.Rel_End_Date) as 'End Date', --change to REL_USER_05 on live DB
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete',
c.cy_user_05 as Primary_Ident,
c.cy_user_06 as Secondary_Ident 
from dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.CY_User_05, c.CY_USER_06, r.Rel_Name

union
----User Story Level Data
select c.CY_CYCLE as 'Name', 
'' as 'Start Date',
'' as 'End Date',
count(distinct ts.TS_NAME) as '# Tests', 
round(cast(sum(case when tc.TC_STATUS = 'Passed' then 1 end) as float) / cast(count(distinct ts.TS_NAME) as float),2)*100 as '% Complete',
r.Rel_Name as Primary_Ident,
'' as Secondary_Ident
from dbo.test ts
, dbo.CYCLE c
, dbo.testcycl tc
, dbo.RELEASE_CYCLES rc
, dbo.RELEASES r
where ts.ts_test_id = tc.tc_test_id
and c.cy_cycle_id = tc.tc_cycle_id
and tc.TC_ASSIGN_RCYC= rc.RCYC_ID
and rc.RCYC_PARENT_ID = r.REL_ID
and c.CY_USER_05 IS NOT NULL
Group By c.CY_User_05, c.CY_USER_06, r.Rel_Name, c.CY_CYCLE

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

Like I've said, put the selects into views or table functions.

A lack of experience doesn't prevent you from doing a good job.

 

[tqeonline]

Qik3Coder - could you give me an example of table functions? I've never used either (to my knowledge).

I am confused with using Squished? and how i could take my current queries down to your level of simplicity.

I gathered all the info from all 4 places then just used Union to get my data. But I don't know how to get it in compressed format.

- Matt

"If I must boast, I will boast of the things that show my weakness"

- Windows 2003 Server, 98 SE, XP
- VB.NET, VSTS 2010, ASP.NET, EXCEL VBA, ACCESS, SQL 2008

 

[Qik3Coder]

CREATE FUNCTION dbo.udfMyTableFunction(@Filter varchar(10))
RETURNS @retArray TABLE (idx smallint IDENTITY(1,1) Primary Key, value varchar(8000))
AS
BEGIN

INSERT INTO @retArray(Value)
SELECT * FROM (
SELECT 'AAA' Val UNION ALL
SELECT 'BBB' UNION ALL
SELECT 'CCC' UNION ALL
SELECT 'ABC' UNION ALL
SELECT 'DEF' UNION ALL
SELECT 'DDD' UNION ALL
SELECT 'EEE') as data
WHERE Val like '%'+@Filter+'%'

RETURN
END

SELECT * FROM dbo.udfMyTableFunction('A') UNION ALL
SELECT * FROM dbo.udfMyTableFunction('D')

HTH,
Lodlaiden

A lack of experience doesn't prevent you from doing a good job.