Uniquely defined Hierarchical Data : Part 3

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 3

Author

Topic

LaurieCox

158 Posts

Posted - 2007-09-11 : 13:21:34

Hi,

Still didn't get any response to my first and second threads on this subject. I think the problem is too complex. So once again I am trying to break it up and clarify with a brand new thread …

I have been working on the problem and I have reduced it some more.

Note: If anybody thinks there is a better approach then what I have started … please let me know

The story so far:

I have a tree that has five categories: Libraries, Problems, Goals, Objectives and Interventions with the following properties:

Libraries can never be children

Problems can only be children of Libraries

Goals can be direct children of Libraries or Problems

Objectives can be direct children of Libraries, Problems or Goals

Interventions can be direct children of Libraries, Problems, Goals or Objectives

Problems, Goals, Objectives and Interventions can be linked to multiple parents

The data comes to me in 9 tables (see following post for script):

Library_Data

Problem_Data

Problem_Link_Data

Goal_Data

Goal_Link_Data

Objective_Data

Objective_Link_Data

Intervention_Data

Intervention_Link_Data

I am trying to format the data into a record set that can be processed by reporting services. This is going to be used to search the libraries for a given string. The user after launching the report will select values for three parameters:

Libraries to search

Categories to search in (P,G,O,I)

Text to search for

After selecting the parameters, reporting services will display a nicely formatted tree of the requested data.

Example:


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

I have been broken down the problem into the following steps:

Get all paths from the <category>_Link_Data tables that meet the given criteria.

Pull the <category>_Name and <category>_Text data into the path data

Break each path into rows with one row per level

Eliminate redundant paths

Get data to order correctly

Ready to write report in reporting services

I have done steps 1 and 2 and am now trying to figure out how to do step 3.

After completing step 2, I will have the following table (run the script in the following post to get this table created and filled with the example data):


create table #ValidPaths
 (ID        varchar(50),
  FACILITY  Int,
  LIBID     Int,
  PROBID    Int,
  GoalID    Int,
  OBJID     Int,
  INTID     Int,
  Library_Name  Varchar(50),
  Problem_Name  Varchar(50),
  Problem_text ntext,
  Goal_Name  varchar(50),
  Goal_Text ntext,
  Objective_Name  varchar(50),
  Objective_Text ntext,
  Intervention_Name  varchar(50),
  Intervention_Text ntext)

I now want to take the data and rearrange it into a table that looks like this:


create table #ExpandedTree
 (CatagoryType char(1), -- A character representing the catagory:
                        -- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
  IndentLevel  Int,     -- When displayed in the tree view the indention level of the item
  ItemName     varchar(50), -- for reasons that do not matter called wiley_<category>_id 
                            -- in the <catagory>_data tables.
  ItemText     ntext)

Example:


CatagoryType	IndentLevel	ItemName		ItemText
L		0		TestLinks3	
P		1		Productivity		Productivity
O		2		Access			Access to Community Resources
L		0		TestLinks3	
P		1		Supportive Needs	Supportive Needs
G		2		Community		Community Participation Level
O		3		Access			Access to Community Resources
L		0		TestLinks3	
P		1		Community		Community Participation Level
G		2		Social			Social Interaction Level
O		3		Activities		Participation in Community Activities
L		0		TestLinks3	
P		1		Supportive Needs	Supportive Needs
G		2		Community		Community Participation Level

Each set of colored rows are generated from one row on the #ValidPaths table. So I think this is some sort of pivot problem, with some twists in calculating the Category and IndentLevel fields.

The secret for IndentLevel is found in the linking table. When a category is the child of what would normally be a grandparent the skipped parent Ids in the <category>_link_table are set to 0.

Example from above:


Problem: Productivity-(P:6)- Productivity
 
      Objective: Access-(O:1265)- Access to Community Resources

The objective (objid:1265) links directly to the Problem(probed:6). This is represented in the Objective_Link_data table with this row:


ID		FACILITY	LIBID	PROBID	GoalID	OBJID
1.1265.11.6.0	1		11	6	0	1265

Therefore when this path is broken down into tree branches, the goal should be at level 2 instead of 3 so that it will indent to be under the library not under some non existent problem.

Note that step 4 is to eliminate redundant paths. If this can be accomplished in step three that would be cool. There are some ordering problems, but I am not thinking about that until I can get the data into separate rows.

All the script needed to get up to step 3 is in the following post.

Please help … especially if I am going about this problem in the wrong way and there is an easier way to do it. I have tried to format the questions and data in the most readable way that I can. Hopefully with each of my threads the actual problem is getting a little clearer.

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2007-09-11 : 13:22:01

[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
select * from #libraryList

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,
Library_Name Varchar(50),
Problem_Name Varchar(50),
Problem_text ntext,
Goal_Name varchar(50),
Goal_Text ntext,
Objective_Name varchar(50),
Objective_Text ntext,
Intervention_Name varchar(50),
Intervention_Text ntext)

/* Interventions */
if charindex('I',@LevelList) > 0
begin
insert into #ValidPaths (ID,FACILITY,LIBID,PROBID,GoalID,OBJID,INTID)
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 (ID,FACILITY,LIBID,PROBID,GoalID,OBJID)
select ID,
FACILITY,
LIBID,
PROBID,
GoalID,
OBJID
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 (ID,FACILITY,LIBID,PROBID,GoalID)
select ID,
FACILITY,
LIBID,
PROBID,
GoalID
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 (ID,FACILITY,LIBID,PROBID)
select ID,
FACILITY,
LIBID,
PROBID
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

-- Add Library Data
Update #ValidPaths
set #ValidPaths.Library_Name = #Library_Data.library_name
from #Library_Data
where #ValidPaths.libid = #Library_Data.libid

-- Add Problem Data
Update #ValidPaths
set #ValidPaths.Problem_Name = #Problem_Data.Wiley_Problem_ID,
#ValidPaths.Problem_Text = #Problem_Data.prob_Text
from #Problem_Data
where #ValidPaths.PROBID = #Problem_Data.PROBID

-- Add Goal Data
Update #ValidPaths
set #ValidPaths.Goal_Name = #Goal_Data.Wiley_Goal_ID,
#ValidPaths.Goal_Text = #Goal_Data.Goal_Text
from #Goal_Data
where #ValidPaths.GOALID = #Goal_Data.GOALID

-- Add Objective_Data
Update #ValidPaths
set #ValidPaths.Objective_Name = #Objective_Data.Wiley_Objective_ID,
#ValidPaths.Objective_Text = #Objective_Data.Objective_Text
from #Objective_Data
where #ValidPaths.OBJID =#Objective_Data.OBJID

-- Add Intervention_Data
Update #ValidPaths
set #ValidPaths.Intervention_Name = #Intervention_Data.Wiley_Intervention_ID,
#ValidPaths.Intervention_Text = #Intervention_Data.Intervention_Text
from #Intervention_Data
where #ValidPaths.INTID =#Intervention_Data.INTID

create table #ExpandedTree
(CatagoryType char(1), -- A character representing the catagory:
-- (L)ibrary, (P)roblem, (G)oal, (O)bjective, (I)ntervention
IndentLevel Int, -- When displayed in the tree view the indention level of the item
ItemName varchar(50), -- for reasons that do not matter called wiley_<category>_id
-- in the <catagory>_data tables.
ItemText ntext)

/* Code to fill #ExpandedTree table see op for expected results*/
[/code]

edit to fix wide window

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources