How can I perform a custom grouping of families? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

How can I perform a custom grouping of families?

Author

Topic

hellhound
Starting Member

1 Post

Posted - 2014-09-22 : 18:00:29

Hi,

I have a task, that for the life of me, i don't seem to find any good way to approach. I need to group members of families that need to be sub-grouped two ways, which I refer to as "Grouping Templates", each of the two ways has it's own set of "rules" to group the members that can be changed by the end user. Currently I have 3 tables that contain the base data:


/****** Object:  Table [dbo].[tbl_People]    ******/

CREATE TABLE [dbo].[tbl_People](
	[Person_ID] [int]  NOT NULL,
	[Family_ID] [int] NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[Sex] [char](1) NOT NULL ,
	[Age] [tinyint] NOT NULL,
 CONSTRAINT [PK_tbl_People] PRIMARY KEY CLUSTERED 
(
	[Person_ID] ASC
)
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_PeopleRelationship](
	[Person_ID_From] [int] NOT NULL,
	[Relationship_ID] [smallint] NOT NULL CONSTRAINT [DF_tbl_PeopleRelationship_Relationship_ID]  DEFAULT ((22)),
	[Person_ID_To] [int] NOT NULL,
 CONSTRAINT [PK_tbl_PeopleRelationship_1] PRIMARY KEY CLUSTERED 
(
	[Person_ID_From] ASC,
	[Person_ID_To] ASC
)
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[tbl_Relationships](
	[Relationship_ID] [smallint]  NOT NULL,
	[RelationshipName] [varchar](50) NULL,
	[OpposingRelationship_ID] [smallint] NULL,
 CONSTRAINT [PK_tbl_Relationships] PRIMARY KEY CLUSTERED 
(
	[Relationship_ID] ASC
)
) ON [PRIMARY]


GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (1, 1, N'Paul', N'Duncan', N'M', 66)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (2, 1, N'Paula', N'Duncan', N'F', 65)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (3, 1, N'Michael', N'Smith', N'M', 61)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (4, 1, N'Michelle', N'Smith', N'F', 61)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (5, 1, N'Oliver', N'Duncan', N'M', 40)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (6, 1, N'Olivia', N'Smith', N'F', 39)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (7, 1, N'Wade', N'Duncan', N'M', 19)
GO
INSERT [dbo].[tbl_People] ([Person_ID], [Family_ID], [FirstName], [LastName], [Sex], [Age]) VALUES (8, 1, N'Wendy', N'Duncan', N'F', 16)
GO

INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 3, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 21, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 21, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 1, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 10, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 5, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (1, 5, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 3, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 21, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 21, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 1, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 10, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 5, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (2, 5, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 21, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 21, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 3, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 10, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 1, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 5, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (3, 5, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 21, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 21, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 3, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 10, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 1, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 5, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (4, 5, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 2, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 2, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 11, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 11, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 19, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 1, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (5, 1, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 11, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 11, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 2, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 2, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 19, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 1, 7)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (6, 1, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 6, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 2, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 2, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (7, 4, 8)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 1)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 2)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 3)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 6, 4)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 2, 5)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 2, 6)
GO
INSERT [dbo].[tbl_PeopleRelationship] ([Person_ID_From], [Relationship_ID], [Person_ID_To]) VALUES (8, 4, 7)

GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (1, N'Parent', 2)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (2, N'Child', 1)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (3, N'Spouse', 3)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (4, N'Sibling', 4)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (5, N'GrandParent', 6)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (6, N'GrandChild', 5)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (7, N'Cousin', 7)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (8, N'Uncle/Aunt', 9)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (9, N'Nephew/Niece', 8)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (10, N'Parent-in-law', 11)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (11, N'Child-in-law', 10)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (12, N'Sibling-in-law', 12)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (13, N'Step-Parent', 14)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (14, N'Step-Child', 13)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (15, N'Step-Sibling', 15)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (16, N'Half-Sibling', 16)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (17, N'Adoptive Parent', 18)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (18, N'Adoptive Child', 17)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (19, N'Couple', 19)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (20, N'Other Blood', 20)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (21, N'Other Non-Blood', 21)
GO
INSERT [dbo].[tbl_Relationships] ([Relationship_ID], [RelationshipName], [OpposingRelationship_ID]) VALUES (22, N'Unknown', 22)
GO

The tbl_People contains the basic information for each individual, tbl_Relationships has the possible relationships that one person can have with another and lastly tbl_PeopleRelationship is a many to many join of the previous tables. If you query them you'll get how each person relates to each other.


SELECT  P1.Person_ID PidF ,
        P1.FirstName ,
        P1.LastName ,
        P1.Sex ,
        P1.Age ,
        PR1 RelationToRight ,
        PR2 RelationToLeft ,
        P2.Person_ID PidT ,
        P2.FirstName ,
        P2.LastName ,
        P2.Sex ,
        P2.Age
FROM    dbo.tbl_PeopleRelationship PR
        INNER JOIN dbo.tbl_People P1 ON PR.Person_ID_From = P1.Person_ID
        INNER JOIN dbo.tbl_People P2 ON PR.Person_ID_To = P2.Person_ID
        INNER JOIN ( SELECT r1.Relationship_ID ,
                            R1.RelationshipName AS PR1 ,
                            R2.RelationshipName AS PR2
                     FROM   tbl_Relationships AS R1
                            INNER JOIN tbl_Relationships AS R2 ON R1.Relationship_ID = R2.OpposingRelationship_ID
                   ) Rels ON PR.Relationship_ID = Rels.Relationship_ID

Possibly i'll have to create a table to save the "rules" each of the "Grouping Templates" similar to this one:


CREATE TABLE [dbo].[tbl_FamilyGroup](
	[FamilyGroup_ID] [int]  NOT NULL,
	[Template_ID] [int] NOT NULL,
	[Relationship_ID] [smallint] NOT NULL,
	[GroupNum] [tinyint] NOT NULL,
	[MinAge] [tinyint] NOT NULL,
	[MaxAge] [tinyint] NOT NULL,
	[MaxMembers] [tinyint] NOT NULL,
	[Priority] [tinyint] NOT NULL,
	[Void] [bit] NOT NULL,
 CONSTRAINT [PK_tbl_FamilyGroup] PRIMARY KEY CLUSTERED 
(
	[FamilyGroup_ID] ASC
)
)

This is a graphic representation of the sample family:
[img]
http://s25.postimg.org/iv1tdhugf/Test_Family_Tree.png
[/img]

With Template A the family would be grouped this way:
[img]
http://s25.postimg.org/cimo3nre7/Test_Family_Tree_TA.png
[/img]

And with Template B they would group like this:
[img]
http://s25.postimg.org/gg9xt2e7j/Test_Family_Tree_TB.png
[/img]

Basically in this example the rules for groupings in this example are:
In Template A is to group any marriages and underage children together with their parents.
Paul/Paula -----> Married
Michael/Michelle -----> Married
Oliver -----> Not Married
Olivia/Wendy -----> Not Married, Underage Child Defaults to Mother
Wade -----> Over 18, Not Married

In Template B we can group marriages and any underage children they have or couples with any underage children they have so you get:
Paul/Paula -----> Married
Michael/Michelle -----> Married
Oliver/Olivia/Wendy ------->Not Married couple with underage children in common
Wade -----> Over 18, Not Married

So finally i need to have a query or process that would give me this result or something similar:

FirstName LastName Sex Age Template Group
Paul Duncan M 66 A 1
Paula Duncan F 65 A 1
Michael Smith M 61 A 2
Michelle Smith F 61 A 2
Oliver Duncan M 40 A 3
Olivia Smith F 39 A 4
Wendy Duncan F 16 A 4
Wade Duncan M 19 A 5
Paul Duncan M 66 B 1
Paula Duncan F 65 B 1
Michael Smith M 61 B 2
Michelle Smith F 61 B 2
Oliver Duncan M 40 B 3
Olivia Smith F 39 B 3
Wendy Duncan F 16 B 3
Wade Duncan M 19 B 4

This would be less difficult if the "rules" were fixed but the end user want to be able to modify grouping parameters like maybe change the age limit for underage children or maybe allow a married couple to group with the nephew if a parent is not in the family. Right now I have no real solution for this, any help with this would be greatly appreciated.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources