SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Help in writing the SP to get this output		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rcchowdhary
Starting Member

United Kingdom
3 Posts
Posted - 08/26/2005 :  05:33:38  Show Profile  Reply with Quote
Hi

I have stucked up with writing stored procedure to get the desired output.would appreciate help in from you all

i would like to get the intersection,union and set of records based on the Type


Here is the data in my table

Resourceclasses
-------------------
ResourceClassId Name Type
-----------------------------------------------
1 Resource Classes 6
2 All Resources 5
3 Set1 5
4 List of Resource 5
5 Union 6
6 Intersect 7
7 Intersection 7
8 Union1 6

Type 5:Set of Resource classes
Type 6:Union of Resource classes
Type 7:Intersection of Resource classes



ResourceclassMembers
----------------------
ResourceClassId SubclassId ResourceId
-------------------------------------------
3 0 1011
3 0 1001
3 0 1015

4 0 1001
4 0 1009
4 0 1004

5 4 0
5 3 0

6 4 0
6 5 0

7 3 0
7 6 0
7 4 0
7 5 0

8 3 0
8 7 0



Desired Output
ResourceClassId ResourceId
3 1011
3 1001
3 1015
---------------------------
5 1011
5 1001
5 1015
5 1009
5 1004
-------------------------------
6 1001
6 1009
6 1004
-----------------------------

Each ResourceclassId in the Resourceclass Table belongs to a Type which is either(5-Set,6-Union,7-Intersection)

which is linked to ResourceClassmembers table

If you look at the ResourceClasses table

ResourceClassId 5 is made up of (Union of)two resourceclasses 3 and 4

so the output for ResourceclassId 5 is
5 1011
5 1001
5 1015
5 1009
5 1004

where as the ResourceclasId 3 is of type(Set)which has no Subclasses so we return ResourceId for them.

3 1011
3 1001
3 1015

simillarly it is same with ResourceClassId 6(of type Intersection)is made up of two classes
4 and 5


Hope i was able to explain my problem clearly..Apologise if any thing wrong with my explanation

Thanks

coolerbob
Aged Yak Warrior

United Kingdom
841 Posts
Posted - 08/26/2005 :  07:05:37  Show Profile  Reply with Quote
show us what u have tried and what is wrong with it
Go to Top of Page

rcchowdhary
Starting Member

United Kingdom
3 Posts
Posted - 08/26/2005 :  07:36:31  Show Profile  Reply with Quote
Thanks for the reply coolerbob

I am trying the solution using lot of temporary tables and cursors.It just looking so awkward solution and it is getting difficult to even debug the sp's

What is the best way to solve this problem
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22469 Posts
Posted - 08/26/2005 :  09:04:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
See if these are helpful
http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000