SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deriving group members from nested groups


Deriving group members from nested groups

Author
Message
Chrissy321
Chrissy321
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14520 Visits: 6246
Hello All,

Any input would be appreciated. I have some working code that derives group members from nested groups. There are two changes I need to make and when I do the code no longer works.
  • I would like to change the data type of my group and member codes to INT from VARCHAR
  • In my new data a group and a member may have the same ID
I have three sets of DDL and sample data in the attached which can be commented/uncommented out
  1. The codes are VARCHAR and there is no overlap between the group and member IDs. This works
  2. The codes are INT and there is overlap between the group and member IDs. This is the scenario I hope to get a solution for.
  3. The codes are INT and there is no overlap between the group and member IDs. This is for demo purposes
Expected results are within the script. the code is rather old and i would definitely be open the the best practice way of solving the problem if this is not it.
Thanks.

--Drop tables if they exist
IF OBJECT_ID('[dbo].[GroupMembers]') IS NOT NULL DROP TABLE dbo.GroupMembers
IF OBJECT_ID('[dbo].[Groups]') IS NOT NULL DROP TABLE dbo.Groups

--1#
--This is the DDL and sample data that works
--The data types are character and there is no overlap between GroupCode and Member

--Create tables
CREATE TABLE dbo.Groups
(
GroupCode VARCHAR(20) NOT NULL,
GroupDescription VARCHAR(20) NULL
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupCode ASC)
)

CREATE TABLE dbo.GroupMembers(
GroupCode VARCHAR(20) NOT NULL,
Member VARCHAR(20) NOT NULL,
MemberType VARCHAR(12) NOT NULL
CONSTRAINT PK_GroupMembers PRIMARY KEY CLUSTERED
(
GroupCode ASC,
Member ASC,
MemberType ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE dbo.GroupMembers WITH CHECK ADD CONSTRAINT FK_GroupMembers_Groups FOREIGN KEY(GroupCode)
REFERENCES dbo.Groups (GroupCode)
GO

INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('A','Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('B','Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('C','Group of Groups')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('D','Mixed')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES ('E','Complicated')
--Group A has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','1','Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('A','2','Individual')
--Group B has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','3','Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('B','4','Individual')
--Group C has two members both of which are groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','A','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('C','B','Group')
--Group D has an individual and a group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','A','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('D','4','Individual')
--Group E has an individual and a group; The group is a nested group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','C','Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES ('E','5','Individual')

--2#
--This is DDL and sample data that does not work
--The data types are INT and there is overlap between GroupCode and Member
/*
--Create tables
CREATE TABLE dbo.Groups
(
GroupCode INT NOT NULL,
GroupDescription VARCHAR(20) NULL
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupCode ASC)
)

CREATE TABLE dbo.GroupMembers(
GroupCode INT NOT NULL,
Member INT NOT NULL,
MemberType VARCHAR(12) NOT NULL
CONSTRAINT PK_GroupMembers PRIMARY KEY CLUSTERED
(
GroupCode ASC,
Member ASC,
MemberType ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE dbo.GroupMembers WITH CHECK ADD CONSTRAINT FK_GroupMembers_Groups FOREIGN KEY(GroupCode)
REFERENCES dbo.Groups (GroupCode)
GO

INSERT INTO Groups (GroupCode,GroupDescription) VALUES (1,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (2,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (3,'Group of Groups')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (4,'Mixed')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (5,'Complicated')
--Group 1 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (1,1,'Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (1,2,'Individual')
--Group 2 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (2,3,'Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (2,4,'Individual')
--Group 3 has two members both of which are groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (3,1,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (3,2,'Group')
--Group 4 has an individual and a group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (4,1,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (4,4,'Individual')
--Group 5 has an individual and a group; The group is a nested group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (5,3,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (5,5,'Individual')
*/

--3#
/*
--Also does not work
--The data types are INT and there is no overlap between GroupCode and Member
--Create tables
CREATE TABLE dbo.Groups
(
GroupCode INT NOT NULL,
GroupDescription VARCHAR(20) NULL
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupCode ASC)
)

CREATE TABLE dbo.GroupMembers(
GroupCode INT NOT NULL,
Member INT NOT NULL,
MemberType VARCHAR(12) NOT NULL
CONSTRAINT PK_GroupMembers PRIMARY KEY CLUSTERED
(
GroupCode ASC,
Member ASC,
MemberType ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE dbo.GroupMembers WITH CHECK ADD CONSTRAINT FK_GroupMembers_Groups FOREIGN KEY(GroupCode)
REFERENCES dbo.Groups (GroupCode)
GO

INSERT INTO Groups (GroupCode,GroupDescription) VALUES (1,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (2,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (3,'Group of Groups')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (4,'Mixed')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (5,'Complicated')
--Group 1 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (1,100,'Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (1,200,'Individual')
--Group 2 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (2,300,'Individual')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (2,400,'Individual')
--Group 3 has two members both of which are groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (3,1,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (3,2,'Group')
--Group 4 has an individual and a group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (4,1,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (4,400,'Individual')
--Group 5 has an individual and a group; The group is a nested group
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (5,3,'Group')
INSERT INTO GroupMembers (GroupCode,Member,MemberType) VALUES (5,500,'Individual')
*/

--Testing for INT datatype
/*
DECLARE @GroupCode INT
SET @GroupCode = 1 --Expected results: 1,2
--SET @GroupCode = 2 --Expected results: 3,4
--SET @GroupCode = 3--Expected results: 1,2,3,4
--SET @GroupCode = 4--Expected results: 1,2,4
SET @GroupCode = 5 --Expected results: 1,2,3,5
*/

--Testing for character datatype
DECLARE @GroupCode VARCHAR(20)
--SET @GroupCode = 'A' --Expected results: 1,2
--SET @GroupCode = 'B' --Expected results: 3,4
SET @GroupCode = 'C'--Expected results: 1,2,3,4
--SET @GroupCode = 'D'--Expected results: 1,2,4
--SET @GroupCode = 'E' --Expected results: 1,2,3,5

--View the group and group members
--SELECT * FROM dbo.Groups
--SELECT * FROM dbo.GroupMembers

;WITH Members AS
(
--Anchor

SELECT
GroupCode,
Member,
0 As isCycle,
'.' + CAST(Member As VARCHAR(max)) + '.' As path
FROM dbo.GroupMembers
WHERE
Member NOT IN (Select GroupCode from GroupMembers)

UNION ALL

--Recursive call

SELECT
h.GroupCode,
h.Member,
CASE WHEN m.path like '%.' + CAST(h.Member as VARCHAR(max)) + '.%' THEN 1 ELSE 0 END As isCycle,
m.path + CAST(h.Member as VARCHAR(max)) + '.' As path
FROM GroupMembers h
INNER JOIN Members m
ON h.member = m.GroupCode
WHERE
isCycle = 0
)

SELECT
DISTINCT --Eliminate duplicate members
--Split the string, extract the member between the first and second periods to get an individual lowest member
LEFT
(
RIGHT
(
path,LEN(path)-1
)
,
charindex('.',RIGHT(path,LEN(path)-2)
)
) AS Member
FROM
Members
WHERE
Members.isCycle = 0 AND
@GroupCode = GroupCode

drew.allen
drew.allen
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71100 Visits: 18742
It's not entirely clear what you are trying to accomplish, but I would restructure your tables. GroupMembers should be a link between groups and individuals ONLY. It should not contain links between groups and subgroups. The links between groups and subgroups should be in your Groups table (or another table).

You could simulate this by filtering your GroupMembers table by whichever type of entity you are currently working with.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Chrissy321
Chrissy321
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14520 Visits: 6246
Thanks Drew. Let me clarify if I can.

I would like to build a groups data structure that allows for a group to contain one or more individual members and/or one or more groups. I would like to allow group of group or nested group functionality.

Given a group identifier (GroupCode) I would like to identify all the individual members. These members would be:
  • all the individual members of the group
  • all the individual members of any group contained within the group (including nested groups)

drew.allen
drew.allen
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71100 Visits: 18742
Chrissy321 - Wednesday, February 21, 2018 3:28 PM
Thanks Drew. Let me clarify if I can.

I would like to build a groups data structure that allows for a group to contain one or more individual members and/or one or more groups. I would like to allow group of group or nested group functionality.
  • all the individual members of the group
  • all the individual members of any group contained within the group (including nested groups)

Let me put this another way. You're using a partial key as if it were a full key. In other words, you need two fields in the group members table to fully identify a particular member: Member and MemberType, but your rCTE is only using the Member field for the recursion, so it cannot distinguish between individual members and group members, and it is treating individuals as if they were subgroups. You need some way to distinguish between individual members and subgroups.


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
drew.allen
drew.allen
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71100 Visits: 18742
I rewrote your query to distinguish between individuals and groups. I did some simplification that may not be warranted. Specifically, I removed the cycle check, because you should not have cycles, but you can always add a depth check to stop processing after a certain depth.


;WITH Members AS
(
--Anchor

SELECT
GroupCode,
Member
FROM #GroupMembers
WHERE MemberType = 'Individual'

UNION ALL

--Recursive call
SELECT h.GroupCode, m.Member
FROM #GroupMembers h
INNER JOIN Members m
ON h.Member = m.GroupCode
WHERE h.MemberType = 'Group'
)

SELECT *
FROM Members m
WHERE @GroupCode = GroupCode


This gives me most of the results that you expect, and the one case where it is different (5), I think that your expected results are wrong.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Chrissy321
Chrissy321
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14520 Visits: 6246
Thank you! Yes my expected results for 5 were wrong. I may post back if I have additional questions or enhancements which could prove useful to others.
Chrissy321
Chrissy321
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14520 Visits: 6246
I added start and end dates to table GroupMembers and added StartDate to the primary key. This is to track when an individual or groups enters/exits a group. I embedded the group derivation logic in a function which accepts two parameters:@GroupCode and @GroupDate. I added some additional test cases. Everything is working as expected. I just wanted to post a follow up in case anyone saw any issues with the code or thought it could be useful to them. Thanks again Drew.

--Drop tables if they exist
IF OBJECT_ID('[dbo].[GroupMembers]') IS NOT NULL DROP TABLE dbo.GroupMembers
IF OBJECT_ID('[dbo].[Groups]') IS NOT NULL DROP TABLE dbo.Groups
GO
--Create tables
CREATE TABLE dbo.Groups
(
GroupCode INT NOT NULL,
GroupDescription VARCHAR(40) NULL
CONSTRAINT PK_Groups PRIMARY KEY CLUSTERED (GroupCode ASC)
)

CREATE TABLE dbo.GroupMembers(
GroupCode INT NOT NULL,
Member INT NOT NULL,
MemberType VARCHAR(12) NOT NULL,
StartDate DATE NOT NULL,
EndDate DATE NULL,
CONSTRAINT PK_GroupMembers PRIMARY KEY CLUSTERED
(
GroupCode ASC,
Member ASC,
MemberType ASC,
StartDate
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
GO

ALTER TABLE dbo.GroupMembers WITH CHECK ADD CONSTRAINT FK_GroupMembers_Groups FOREIGN KEY(GroupCode)
REFERENCES dbo.Groups (GroupCode)
GO
--Drop function if exists
IF OBJECT_ID('GetGroupMembers') IS NOT NULL DROP FUNCTION GetGroupMembers
GO
--Create function
CREATE FUNCTION dbo.GetGroupMembers
(
@GroupCode INT,
@GroupDate DATE
)
RETURNS TABLE AS
RETURN

WITH Members AS
(
--Anchor
SELECT
GroupCode,
Member
FROM GroupMembers
WHERE
@GroupDate BETWEEN StartDate AND COALESCE(EndDate,GETDATE()) AND
MemberType = 'Individual'

UNION ALL

--Recursive call
SELECT
h.GroupCode,
m.Member
FROM GroupMembers h
INNER JOIN Members m
ON h.Member = m.GroupCode
WHERE
@GroupDate BETWEEN StartDate AND COALESCE(EndDate,GETDATE()) AND
h.MemberType = 'Group'
)

SELECT
Member
FROM Members m
WHERE
@GroupCode = GroupCode

GO
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (1,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (2,'Simple group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (3,'Group of Groups')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (4,'Mixed')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (5,'Complicated')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (6,'Different Start Dates')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (7,'Different Start Dates and an End Date')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (8,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (9,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (10,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (11,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (12,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (13,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (14,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (15,'additional test group')
INSERT INTO Groups (GroupCode,GroupDescription) VALUES (16,'additional test group')

--Group 1 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (1,1,'Individual','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (1,2,'Individual','2018-02-01',NULL)
--Group 2 has two individual members; no nested groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (2,3,'Individual','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (2,4,'Individual','2018-02-01',NULL)
--Group 3 has two members both of which are groups
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (3,1,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (3,2,'Group','2018-02-01',NULL)
--Group 4 has an individual and a group
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (4,1,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (4,4,'Individual','2018-02-01',NULL)
--Group 5 has an individual and a group; The group is a nested group
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (5,3,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (5,5,'Individual','2018-02-01',NULL)

DECLARE
@GroupCode INT,
@GroupDate DATE

--GroupCode = 1; GroupDate = '2018-02-15'
SET @GroupCode = 1
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 1; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: Members 1 and 2' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 2; GroupDate = '2018-02-15'
SET @GroupCode = 2
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 2; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: Members 3,4' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 3; GroupDate = '2018-02-15'
SET @GroupCode = 3
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 3; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: 1,2,3,4' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 4; ; GroupDate = '2018-02-15'
SET @GroupCode = 4
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 4; ; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: 1,2,4' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 5; GroupDate = current date
SET @GroupCode = 5
SET @GroupDate = GETDATE()
SELECT
'GroupCode = 5; GroupDate = current date' AS Test,
Member,
'Expected results: 1,2,3,4,5' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--Start testing the start and end date logic

--Create new groups
--Group 6 has two members both of which are groups; Group 1 joined on 2018-02-01 and Group 2 joined on 2018-02-02
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (6,1,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (6,2,'Group','2018-02-02',NULL)

--GroupCode = 6; GroupDate = '2018-02-15';
SET @GroupCode = 6
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 6; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: The individual members of both groups 1 and 2: 1,2,3,4. All existed in the group on 2018-02-15' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 6; GroupDate = '2018-02-15';
SET @GroupCode = 6
SET @GroupDate = '2018-02-15'
SELECT
'GroupCode = 6; GroupDate = 2018-02-15' AS Test,
Member,
'Expected results: The individual members of both groups 1 and 2; 1,2,3,4 All existed in the group on 2018-02-02' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 6; GroupDate = '2018-02-01';
SET @GroupCode = 6
SET @GroupDate = '2018-02-01'
SELECT
'GroupCode = 6; GroupDate = 2018-02-01' AS Test,
Member,
'Expected results: The individual members of group 1 ; Members 1,2; Only group 1 existed in the group on 2018-02-01' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 6; GroupDate = '2017-02-01';
SET @GroupCode = 6
SET @GroupDate = '2017-02-01'
SELECT
'GroupCode = 6; GroupDate = 2017-02-01' AS Test,
Member,
'Expected results: None; No groups existed on 2017-02-01' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--Group 7; Sames as group 6 but group 1 exited the group on 2018-02-05
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (7,1,'Group','2018-02-01','2018-02-05')
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (7,2,'Group','2018-02-02',NULL)

--GroupCode = 7; GroupDate = '2018-02-05';
SET @GroupCode = 7
SET @GroupDate = '2018-02-05'
SELECT
'GroupCode = 7; GroupDate = 2018-02-05' AS Test,
Member,
'Expected results: The individual members of both groups 1 and 2; 1,2,3,4 All existed in the group on 2018-02-05' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 7; GroupDate = '2018-02-06';
SET @GroupCode = 7
SET @GroupDate = '2018-02-06'
SELECT
'GroupCode = 7; GroupDate = 2018-02-06' AS Test,
Member,
'Expected results: 3 and 4; Group 1 exited group 7 on 2018-02-05' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 7; GroupDate = '2018-02-01'
SET @GroupCode = 7
SET @GroupDate = '2018-02-01'
SELECT
'GroupCode = 7; GroupDate = 2018-02-01' AS Test,
Member,
'Expected results: The individual members of group 1; Group 2 joined group 7 the next day' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--Nest deeper
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (8,9,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (9,10,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (10,11,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (11,1,'Individual','2018-02-01',NULL)

--GroupCode = 8; GroupDate = '2018-02-01';
SET @GroupCode = 8
SET @GroupDate = '2018-02-01'
SELECT
'GroupCode = 8; GroupDate = 2018-02-01' AS Test,
Member,
'Expected results: Member 1; Group 8 contains group 9 which contains group 10 which contains group 11; Group 11 contains the individual member 1' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--Testing group 9, 10 and 11 will get the same result
SET @GroupCode = 11
SET @GroupDate = '2018-02-01'
SELECT
'GroupCode = 11; GroupDate = 2018-02-01' AS Test,
Member,
'Expected results: Member 1' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--Test when an intermediate nested group has an individual member
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (12,13,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (13,14,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (13,8,'Individual','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (14,15,'Group','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (15,1,'Individual','2018-02-01',NULL)

--GroupCode = 12; GroupDate = '2018-02-01';
SET @GroupCode = 12
SET @GroupDate = '2018-02-01'
SELECT
'GroupCode = 12; GroupDate = 2018-02-01' AS Test,
Member,
'Expected results: 1 and 8; Group 12 contains group 13 which contains group 14 (and individual 8) which contains group 15; Group 15 contains the member 1' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--A group exits and re-enters a group; Has an individual from the start
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (16,19,'Individual','2018-02-01',NULL)
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (16,1,'Group','2018-02-01','2018-02-05')
INSERT INTO GroupMembers (GroupCode,Member,MemberType,StartDate,EndDate) VALUES (16,1,'Group','2018-02-10','2018-02-15')

--GroupCode = 16; GroupDate = '2018-02-05';
SET @GroupCode = 16
SET @GroupDate = '2018-02-05'
SELECT
'GroupCode = 16; GroupDate = 2018-02-05' AS Test,
Member,
'Expected results: Members 1, 2 and 19; Group 1 which contains members 1 and 2 was in group 16 on 2018-02-05' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--GroupCode = 16; GroupDate = '2018-02-09';
SET @GroupCode = 16
SET @GroupDate = '2018-02-09'
SELECT
'GroupCode = 16; GroupDate = 2018-02-09' AS Test,
Member,
'Expected results: Just 19; Group 1 which contains members 1 and 2 was not in group 16 on 2018-02-09' AS ExpectedResults
FROM dbo.GetGroupMembers(@GroupCode,@GroupDate)

--View the group and group members
--SELECT * FROM dbo.Groups
--SELECT * FROM dbo.GroupMembers


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search