Recursion - Items in the same table but Children?

[tqeonline]

Hello all!

I have a table [REQ] it has Projects, Iterations, Releases, and User Stories.

I have to come up with a way to report Project metrics. So I can get all the metrics I want from the User Story level (i believe). The issue is making it all appear like I want.

If i can just get the Project to show up as a row, Iterations to show up as a few rows, REleases to show up as rows, and User Stories to show up as rows then i'd be ok.

This is what I have so far (only Project and Iteration):

SELECT PRJ.RQ_REQ_NAME
FROM [REQ] PRJ
where RQ_REQ_ID = '2523'
GROUP BY PRJ.RQ_REQ_ID,PRJ.RQ_REQ_NAME

union

SELECT IT.RQ_REQ_NAME
FROM [REQ] IT
where RQ_FATHER_ID = '2523'
GROUP BY IT.RQ_REQ_ID, IT.RQ_REQ_NAME

So the goal is to have each layer it's own row without having the "WHERE RQ_FATHER_ID..." - if i have 30 projects going on that all have 4 iterations that all have 5 releases with 5 user stories each then I want my solution to be scaleable.

So I need to some how Union Projects to Iterations to Releases to User Stories. Once I'm at the User Story level (3 layers deep) I can gather the data I need to use.

- 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

 

[gmmastros]

Can you post some sample data and expected results? It will help us to help you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tqeonline]

of course:
*project*
RQ_REQ_ID: 2517
RQ_REQ_ID: 2523
RQ_REQ_NAME: Brad and Matt Test Project (PRJ0549)

*Iteration*
RQ_PARENT_ID: 2523
RQ_REQ_ID: 2527
RQ_REQ_NAME: Iteration 1

*Release*
RQ_PARENT_ID: 2527
RQ_REQ_ID: 2528
RQ_REQ_NAME: iCare Sprint 47

*User Story*
RQ_PARENT_ID: 2528
RQ_REQ_ID: 2524
RQ_REQ_NAME: US 654

*User Story*
RQ_PARENT_ID: 2528
RQ_REQ_ID: 2526
RQ_REQ_NAME: US 987

so then the results would be:

[1] Brad and MAtt Test Project (PRJ-549)
[2] Iteration 1
[3] iCare Sprint 47
[4] US 654
[5] US 987

And I will have to some how come up with a way to have ID's for them so I can rebuild the structure on the ASP side.

- 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

 

[tqeonline]

SELECT Null as ID,
	Null as Parent_ID,
	PRJ.RQ_REQ_NAME,
	'0' as Primary_Ident,
	Null as Secondary_Ident
FROM [REQ] PRJ
where RQ_FATHER_ID = '2517' --RQ_REQ_ID = '2523'

union

SELECT Null as ID,
	Null as Parent_ID,
	IT.RQ_REQ_NAME,
	PRJ.[RQ_REQ_NAME] as Primary_Ident,
	Null as Secondary_Ident
FROM [REQ] IT, [REQ] PRJ
where IT.[RQ_FATHER_ID] = PRJ.[RQ_REQ_ID]
AND PRJ.[RQ_FATHER_ID] = '2517'

This displays:

ID Parent_ID RQ_REQ_NAME Primary_Ident Secondary_Ident
NULL NULL Brad and Matt Test Project (PRJ-549) 0 NULL
NULL NULL Iteration1 Brad and Matt Test Project (PRJ-549) NULL

But that is still based off of a Father_ID

- 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

 

[gmmastros]

I would encourage you to take a look at this blog. It does a good job of explaining recursive CTE's.

http://blog.sqlauthority.com/2008/07/28/sql-server-simple-example-of-recursive-cte/

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom