Uniquely defined Hierarchical Data : Part 2

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

SQL Server 2000 Forums

Transact-SQL (2000)

Uniquely defined Hierarchical Data : Part 2

Author

Topic

LaurieCox

158 Posts

Posted - 2007-09-07 : 15:34:44

Hi,

I didn't get any responses for my first thread. This thing is very complicated. I am having a hard time wrapping my brain around it, which makes it hard for me to explain.

Anyway I have now broken the problem into two steps:

Get all paths that meet the user's criteria

Break Paths into reporting rows

I have completed the first step (see next post for code). This code creates a ValidPath table that has all of the linking rows from the <level type>_link_data tables where the associated text data (from the <level type>_data tables) meets the user's criteria (stored procedure parameters).

What I now want to do is take the data in the ValidPaths table and the text data from each <level type>_data table and create a row set that I can then process in Reporting Services to create the desired output.

I have changed the example data to more "real world" type data which makes it a little easier to read (the data statements for this example are in the post following this post. I also included the create table statements for ease in regenerating the tables with the correct data).


Tree view of example data:
 (I put the <level>ID column values for ease in looking between the tree view and a the table view).

Problem: Productivity-(P:6)- Productivity
 
      Objective: Access-(O:1265)- Access to Community Resources
 
Problem: Supportive Needs-(P:7)- Supportive Needs
 
      Goal: Community-(G:427)- Community Participation Level
 
           Objective: Access-(O:1265)- Access to Community Resources
 
               Intervention: Transportation-(I:2467)- Provide Transportation
 
           Objective: Activities-(O:1266)- Participation in Community Activities
 
      Goal: Social-(G:428)- Social Interaction Level
  
           Objective: Activities-(O:1266)- Participation in Community Activities
 
           Objective: Groups-(O:1264)- Participation in Social Groups


Search Parameters: 
	Library: TestLink3
	Levels to search: Goals and Objectives, 
	SearchString: Community

Desired Search Results:
Problem: Productivity-(P:6)- Productivity
      Objective: Access-(O:1265)- Access to Community Resources
 
Problem: Supportive Needs-(P:7)- Supportive Needs
      Goal: Community-(G:427)- Community Participation Level
           Objective: Access-(O:1265)- Access to Community Resources
 
           Objective: Activities-(O:1266)- Participation in Community Activities
 
      Goal: Social-(G:428)- Social Interaction Level
           Objective: Activities-(O:1266)- Participation in Community Activities

If you run the code in the following post you should get the following data for the ValidPath table:


ID			FACILITY	LIBID	PROBID	GOALID	OBJID	INTID
1.1265.11.6.0		1		11	6	0	1265	NULL
1.1265.11.7.427		1		11	7	427	1265	NULL
1.1266.11.7.427		1		11	7	427	1266	NULL
1.1266.11.7.428		1		11	7	428	1266	NULL
1.427.11.7		1		11	7	427	NULL	NULL

Note that Objective 1265 links directly to problem 6 skipping a Goal. It also links to Goal 427 which in turn links to problem 7.

So my question is how do I get from the data in ValidPath to the data formatted for Reporting Services or what is a good design of the Stored Procedures output and how do I manipulate the data?

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-09-07 : 15:35:19

[code]
/****************** library *********************/

CREATE TABLE #Library_Data
(ID varchar(254),
FACILITY Int,
library_year varchar(50),
LIBID Int,
Library_Name varchar(50),
entry_date Datetime,
entry_time varchar(50),
entry_by varchar(50),
effective_date Datetime,
expiration_date Datetime,
library_origin varchar(50),
Wiley_Library_ID varchar(50),
active_code varchar(50),
active_value varchar(50))

/****************** Problems *********************/

CREATE TABLE #Problem_Data
(ID varchar(254),
FACILITY Int,
PROBID Int,
prob_Text ntext,
Wiley_Problem_ID varchar(50),
suggested_diagnosis ntext)

CREATE TABLE #Problem_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int)

/****************** Goals *********************/

CREATE TABLE #Goal_Data
(ID varchar(254),
FACILITY Int,
GOALID Int,
goal_text ntext,
Wiley_Goal_ID varchar(50))

CREATE TABLE #Goal_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GOALID Int)

/****************** Objectives *********************/

CREATE TABLE #Objective_Data
(ID varchar(254),
FACILITY Int,
OBJID Int,
objective_text ntext,
Wiley_Objective_ID varchar(50))

CREATE TABLE #Objective_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GOALID Int,
OBJID Int)

/****************** Interventions *********************/

CREATE TABLE #Intervention_Data
(ID varchar(254),
FACILITY Int,
INTID Int,
intervention_text ntext,
Wiley_Intervention_ID varchar(50))

CREATE TABLE #Intervention_Link_Data
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GOALID Int,
OBJID Int,
INTID Int)

INSERT INTO #library_data
SELECT'1||11',1,'1840',11,'TestLinks3', '2007-09-07 00:00:00.000',
'Laurie Cox', 'Laurie Cox', '2007-09-07 00:00:00.000', '', 'test', '', 'N', 'No'

INSERT INTO #Problem_Data
SELECT'1||6',1,6,'Productivity', 'Productivity', '' UNION ALL
SELECT'1||7',1,7,'Supportive Needs', 'Supportive Needs', ''

INSERT INTO #Problem_Link_Data
SELECT'1.6.11',1,11,6 UNION ALL
SELECT'1.7.11',1,11,7

INSERT INTO #goal_data
SELECT'1||427',1,427,'Community Participation Level', 'Community' UNION ALL
SELECT'1||428',1,428,'Social Interaction Level', 'Social'

INSERT INTO #Goal_Link_Data
SELECT'1.427.11.7',1,11,7,427 UNION ALL
SELECT'1.428.11.7',1,11,7,428

INSERT INTO #Objective_Data
SELECT'1||1264',1,1264,'Participation in Social Groups', 'Groups' UNION ALL
SELECT'1||1265',1,1265,'Access to Community Resources', 'Access' UNION ALL
SELECT'1||1266',1,1266,'Participation in Community Activities', 'Activities'

INSERT INTO #Objective_Link_Data
SELECT'1.1265.11.6.0',1,11,6,0,1265 UNION ALL
SELECT'1.1265.11.7.427',1,11,7,427,1265 UNION ALL
SELECT'1.1266.11.7.427',1,11,7,427,1266 UNION ALL
SELECT'1.1264.11.7.428',1,11,7,428,1264 UNION ALL
SELECT'1.1266.11.7.428',1,11,7,428,1266

INSERT INTO #Intervention_Data
SELECT'1||2467',1,2467,'Provide Transportation', 'Transportation'

INSERT INTO #Intervention_Link_Data
SELECT'1.2467.11.7.427.1265',1,11,7,427,1265,2467

/*************************************************************************/

/* Step one: Get all paths that meet the user's criteria */

/* Parameters:
LibraryList = csv string
LevelList = csv string
StringFind = wild carded string
*/

create table #LibraryList
(
libid int
)
insert into #LibraryList
--select 10 union all
select 11

declare @StringFind varchar(100)
set @StringFind = '%community%'

declare @LevelList varchar(10)
set @LevelList = 'O,G'

create table #ValidPaths
(ID varchar(50),
FACILITY Int,
LIBID Int,
PROBID Int,
GOALID Int,
OBJID Int,
INTID Int)

/* Interventions */
if charindex('I',@LevelList) > 0
begin
insert into #ValidPaths
select ID,
FACILITY,
LIBID,
PROBID,
GOALID,
OBJID,
INTID
from #Intervention_link_Data il
where exists
(select i.intid
from #Intervention_Data i
where i.Intervention_text like @StringFind
and i.intid = il.intid)
and il.libid in (select * from #LibraryList)
end

/*Objectives */
if charindex('O',@LevelList) > 0
begin
insert into #ValidPaths
select ID,
FACILITY,
LIBID,
PROBID,
GOALID,
OBJID,
null
from #Objective_link_Data ol
where exists
(select o.objid
from #Objective_Data o
where o.Objective_text like @StringFind
and o.objid = ol.objid)
and ol.libid in (select * from #LibraryList)
end

/* Goals */
if charindex('G',@LevelList) > 0
begin
insert into #ValidPaths
select ID,
FACILITY,
LIBID,
PROBID,
GOALID,
null,
null
from #Goal_link_Data gl
where exists
(select g.GOALID
from #Goal_Data g
where g.Goal_text like @StringFind
and g.GOALID = gl.GOALID)
and gl.libid in (select * from #LibraryList)
end

/* Problems */
if charindex('P',@LevelList) > 0
begin
insert into #ValidPaths
select ID,
FACILITY,
LIBID,
PROBID,
null,
null,
null
from #Problem_link_Data pl
where exists
(select p.PROBID
from #Problem_Data p
where p.Prob_text like @StringFind
and p.PROBID = pl.PROBID)
and pl.libid in (select * from #LibraryList)
end

select * from #ValidPaths
[/code]

P.S. I will be replacing the create table statements with declare table statements and doing away with the *'s when I clean it up for the sp. But I welcome any constructive criticism on this code. Not on the original table structures (that I cannot change).

Thanks,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources