Generating combinations in SQL Server

I have a table that contains groups ('G1', 'G2' etc) and a table that contains persons ('P1', 'P2', etc...) and a m:m relation ship between them, so one user can belong to several g开发者_C百科roups, and one group consists of several users.

I have a rule that is satisfied only if a certain number of members of each group is present (i.e. at least 2 members of G1 and at least 1 member of G2 must be present), and I have a list od users that are present. One person cannot fulfil more than one requirement, so if P1 and P2 are members of both G1 and G2, the rule still needs a third person which can be a member of either G1 or G2.

Any ideas how can this be done in SQL Server?

Creation scripts:

create table Groups (GroupID int, Name nvarchar(100))
insert into Groups values (1, 'First')
insert into Groups values (2, 'Second')
insert into Groups values (3, 'Third')

create table Persons (PersonID int, Name nvarchar(100))
insert into Persons values (1, 'One')
insert into Persons values (2, 'Two')
insert into Persons values (3, 'Three')
insert into Persons values (4, 'Four')
insert into Persons values (5, 'Five')
insert into Persons values (6, 'Six')

create table PersonGroups (PersonID int, GroupID int)
-- p1 and p2 are members of g1
insert into PersonGroups values (1, 1)
insert into PersonGroups values (2, 1)
-- p2, p3 and p4 are members of g2
insert into PersonGroups values (2, 2)
insert into PersonGroups values (3, 2)
insert into PersonGroups values (4, 2)
-- p2, p4, p5 and p6 are members of g3
insert into PersonGroups values (2, 3)
insert into PersonGroups values (4, 3)
insert into PersonGroups values (5, 3)
insert into PersonGroups values (6, 3)

So, If a rule needs one person from each group to be present (1,3,5), (1,2,3), (2,3,4) would be valid, and (3, 5, 6) would not be valid.

Create header table for rules

create table #ruleset (Id int, name varchar(100))
insert into #ruleset
select 1,'At least 1 person from each group'

Create child table for each rule having many entries for each group.

drop table #ruleset_grouprules
create table #ruleset_Grouprules(Id int identity(1,1), RuleId int, 
     GroupID int, MinUsers int, MaxUsers int)
insert into #ruleset_Grouprules (RuleId, groupId, MinUsers, MaxUsers)
select 1,1,1,null
union all
select 1,2,1,null
union all
select 1,3,1,null

• You can use NULL in the MinUsers column to represent no minimum amount
• You can use NULL in the MaxUsers column to represent no maximum amount

This query will show you whether the group rules have passed or not.

select r.id, r.Name, gr.GroupId,
    case when x.GroupQty>=isnull(gr.MinUsers, x.GroupQty) 
      and x.GroupQty<=isnull(gr.MaxUsers, x.GroupQty) 
    then 1 else 0 end as GroupValid
from #ruleset r
join #ruleset_Grouprules gr on gr.RuleId=r.Id
join (
    select g.groupID, count(*) GroupQty
    from #Groups g
    join #PersonGroups pg on pg.GroupID=g.GroupID
    join #Persons p on p.PersonID=pg.PersonID
    group by g.GroupID
)x on x.GroupID=gr.GroupID

You can then aggregate on this query to compare sum(GroupValid)=count(*) with a group by r.id to check if the entire Rule is valid. I left it like that so you can see the working data.