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 |
|
kkmurthy
Starting Member
41 Posts |
 Posted - 2008-04-30 : 10:35:11
|
| 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 ID1 Program14 Null2 Project1 13 Taskorder1 24 Taskorder2 2I have another table survey table which is a child table of Projecttable( 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 scoreID3 1 1 13 1 2 13 1 3 23 1 4 33 1 5 24 1 1 14 1 2 14 1 3 24 1 4 34 1 5 2In 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 havethe values as :scoreID count 1 2 2 2 3 1and for task order2 then I will havethe values as :scoreID count 1 2 2 2 3 1The 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 project1I should get the data as follows for Project1:the values as :scoreID count 1 4 2 4 3 2 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
|
|
|
I desperately need help on this problem please