I desperately need help - SQL Server Forums

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)

I desperately need help

Author

Topic

kkmurthy
Starting Member

41 Posts

Posted - 2008-04-30 : 10:37:43

I am using SQL server 200 databae.
I have project table containing ID field and parentID field.
In this table I have three levels: program, project and taskorder. taskorder is a child of Project and project is child of program.
For example the table looks like below with ID, name and ParentID field :
ID Name parent ID
1 Program14 Null
2 Project1 1
3 Taskorder1 2
4 Taskorder2 2

I have another table survey table which is a child table of Project
table( project.ID= survey.projectID). This table contains the data for scores at the lowest level only ( that is task order in this case)The scoreID has only 3 values:1,2,3

the data in survey table lokks as folows:
projectID monthID questionID scoreID
3 1 1 1
3 1 2 1
3 1 3 2
3 1 4 3
3 1 5 2
4 1 1 1
4 1 2 1
4 1 3 2
4 1 4 3
4 1 5 2

In the above data for the projectID=3(taskordeer1) if I countthe number of 1's,2's and 3's in scoreID and group it for task order1 then I will have
the values as :scoreID count
1 2
2 2
3 1
and for task order2 then I will have
the values as :scoreID count
1 2
2 2
3 1

The question I have is :
In the above scenario how will I roll up the total of taskorder 3 and tasak order 4 into its parent project1 (using query only with out writing procedure and creating another table for totals):
in other words when I roll up for project1
I should get the data as follows for Project1:
the values as :scoreID count
1 4
2 4
3 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 10:49:42

Are you using 2000 or 2005? If using 2005 search for recursive CTE in books online. If 2000, search for expanding hierarchies.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-30 : 11:02:38

When dealing with hierarchical data like this, it can be useful to create a function which returns the 'path' of ancestors. You can then use that path in various ways...

-- Structure and data
create table MyProject (ID int, Name varchar(30), parentID int)
insert MyProject
          select 1, 'Program14', Null
union all select 2, 'Project1', 1
union all select 3, 'Taskorder1', 2
union all select 4, 'Taskorder2', 2

create table MySurvey (projectID int, monthID int, questionID int, scoreID int)
insert MySurvey
          select 3, 1, 1, 1
union all select 3, 1, 2, 1
union all select 3, 1, 3, 2
union all select 3, 1, 4, 3
union all select 3, 1, 5, 2
union all select 4, 1, 1, 1
union all select 4, 1, 2, 1
union all select 4, 1, 3, 2
union all select 4, 1, 4, 3
union all select 4, 1, 5, 2
go

-- Function
create function dbo.AncestorPath(@ID int) returns varchar(100) as
begin
    declare @Path varchar(100)
    while 0 = 0
    begin
        select @Path = cast(ID as varchar(5)) + isnull('/' + @Path, ''), @ID = parentID
        from dbo.MyProject where ID = @ID

        if @@rowcount = 0 break
    end

    return @Path
end
go

-- Calculation
select ID, scoreID, count(*) as cnt, dbo.AncestorPath(ID) as Path
from MyProject a left outer join MySurvey b on '/' + dbo.AncestorPath(b.projectID) + '/' like '%/' + cast(a.ID as varchar(10)) + '/%'
group by ID, scoreID order by ID, scoreID

/* Results
ID          scoreID     cnt         Path
----------- ----------- ----------- ----------
1           1           4           1
1           2           4           1
1           3           2           1
2           1           4           1/2
2           2           4           1/2
2           3           2           1/2
3           1           2           1/2/3
3           2           2           1/2/3
3           3           1           1/2/3
4           1           2           1/2/4
4           2           2           1/2/4
4           3           1           1/2/4
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-30 : 11:03:48

See also these:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101967

I think today must be hierarchy day.