February 21, 2018 at 11:11 am
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.
--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
February 21, 2018 at 2:03 pm
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
February 21, 2018 at 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.
Given a group identifier (GroupCode) I would like to identify all the individual members. These members would be:
February 22, 2018 at 9:51 am
Chrissy321 - Wednesday, February 21, 2018 3:28 PMThanks 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
February 22, 2018 at 1:04 pm
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
February 26, 2018 at 11:29 am
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.
February 26, 2018 at 4:43 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply