Wednesday, May 8, 2013

[SQL Server] Creating a hierarchy group from one table column containing both parent and child hierarchy info

[SQL Server] Creating a hierarchy group from one table column containing both parent and child hierarchy info

Creating a hierarchy group from one table column containing both parent and child hierarchy info

Posted: 08 May 2013 05:50 AM PDT

HiI need help creating a hierarchy group 'using' the data in [b]Event_Role[/b] column in my sample table to create a grouping structure like below – The 6 parent hierarchies which are 'Speaker', 'Coordinator', 'Volunteer', 'Instructor', 'Attendee', 'Registration'And the child hierarchies (or lower levels) are Did Not Attend/Yes Attend for Attendee and Do Not Register/No Reg/Yes Reg for Registration.I have 100000+ rows to create these hierarchy groups for and I have more groups and child hierarchies. [b]hierarchy Groups[/b] Speaker Coordinator Volunteer Instructor Attendee--- [b]child(Lv2)[/b]-- Did Not Attend/Yes AttendRegistration--[b]child(Lv2)[/b]---Do Not Register/No Reg/Yes RegI have create the sample data in SQL below -- Thank you in Advance!![hr][code]Create table table1(Participant_ID int not null,Supporter_ID int not null,Event_Code varchar (50),Event_Role varchar (100) null)INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234221','23536','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234220','17238','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234219','23532','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234218','58195','LEG_HAT_1996','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Did Not Attend');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234217','23494','LEG_SWI_1995','Yes Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234208','00442','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234182','65306','SWCM01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234152','22969','LEG_HAT_1996','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','IsCoordinator');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234146','32672','LEG_MAR_01','No Reg');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234123','47377','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234122','38726','LEG_OXF_1998','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('234121','50341','LEG_BUR_0000','Yes Attended');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218893','90409','SEMF091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218890','23749','RFSEAH091212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218889','90387','SEAJ251112','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218884','65306','SERB031212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218874','58902','SECW021212','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218231','50766','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218221','73854','SEGF050912','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218218','28146','LM1301','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('218211','95575','SEDB220712','Speaker');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('217647','22632','GNR0001','Do Not Register');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142470','08862','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('142469','01362','ELON09','IsVolunteer');INSERT INTO Table1 (Participant_ID,Supporter_ID,Event_Code,Event_Role) VALUES ('132899','92898','ELON09','IsVolunteer');[/code]

Find Orphan records in a table

Posted: 08 May 2013 06:48 AM PDT

I have a table which follows a hierarchy and I am trying to find the orphan records.The table has two columns:UnitID ParentUnitID631 0632 631633 632634 633635 633It appears UnitID 631 is deleted and so I am left with orphan records (632,633,634 and 635) which I need to delete. I have many other orphan records like these. Is there an easy way to identify the orphan records using SQL and delete them?

Queries for Multiple Databases

Posted: 08 May 2013 12:58 AM PDT

I am trying to write a query that will search for databases created in the last week. Once I get that info I need to query all the databases returned for information in a table that they all will have.I know I can write the first part by querying sys.databases and using datediff to pull the ones created.I don't know how to take that list and query them all for information maintained in the specific table.exp..db1.table1db2.table1db3.table1all 3 tables would have info in one column that I'm trying to return.thanks for any help

Database Design Best Practice for Generic Objects

Posted: 08 May 2013 01:52 AM PDT

I have general question regarding design practices for generic child objects which may belong to many different types of parent objects. In my case, I'm keeping a note history table that has the columns NOTE_ID (Int), OWNER_ID (GUID), NOTE (nvarchar), MODIFIED_BY (int), MODIFIED_DATE (datetime). The note has a many-to-one relationship with the owner. Instead of having a Note Table for each different type of object that has notes, I was trying to use a single table. This simplifies the design, but I seem to be compromising on integrity, since I can no longer use a foreign key on the owner_id column. If I were to write a sproc to delete a parent object, I'm going to have to remember that I need to delete the Notes for the parent as well. If I had a separate note table for each parent, I could use a foreign key and have a cascade delete. It seems to be a trade off. I was just wondering if there's anything I'm missing? Or if there is a best practice for this scenario?Thanks much
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog