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.
| 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 |
 |
|
|
|
|
|
|
|
Uniquely defined Hierarchical Data : Part 3