All geek questions in one place

How to check gate combinations with t-sql?

I have to check if the worker has all the necessary skills. This is done by comparing a set of working skills with a set of necessary skills. So, to make this clearer, here are the DDL tables that I have:

CREATE TABLE [WorkerSkills](
    [WorkerId] [bigint] NOT NULL,
    [SkillName] [varchar](100) NOT NULL
) GO

CREATE TABLE [SkillCombinator](
    [SetId] [int] NOT NULL,
    [SkillCombinator] [varchar](5) NOT NULL
) GO

CREATE TABLE [RequiredSkills](
    [SetId] [int] NOT NULL,
    [SkillName] [varchar](100) NOT NULL
) GO

and here is an example of data:

INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'A')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'B')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'C')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'D')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'X')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (3, 'E')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'A')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'B')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'H')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'I')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'A')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'B')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'C')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'E')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'G')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'H')
INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'I')

INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (1, 'AND')
INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (2, 'OR')
INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (3, 'AND')

INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'A')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'B')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'C')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'D')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'E')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'F')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'G')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'H')
INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'I')

This means that there are 3 sets, each of which has 3 specific skills.

set 1: A and B and C
set 2: D or E or F
set 3: G and H and I

And there are workers with the following skills:

worker 1: A, B, C
worker 2: D, X
worker 3: E
worker 4: A, B, H, I
worker 5: A, B, C, E, G, H, I

Now the problem is to write a function in Sql Server 2008 that accepts the WorkerId and SetCombinator parameters and returns a value indicating whether Worker has all the necessary skills.

Input Example 1:

WorkerId: 1
SetCombinator: OR

This means that all sets must have a combinator OR , i.e.:

set 1: A and B and C
OR
set 2: D or E or F
OR
set 3: G and H and I

true, , # 1.

2:

WorkerId: 4
SetCombinator: OR

false.

3:

WorkerId: 1
SetCombinator: AND

, , :

set 1: A and B and C
AND
set 2: D or E or F
AND
set 3: G and H and I

false, , # 1, 2- 3- .

4:

WorkerId: 5
SetCombinator: AND

true, , .

, ?

: , RequiredSkills , , . , .

+4
3

, , .

create function fnMatchedToSkillsSet
(
 @WorkerId int,
 @Condition varchar(3)
)
returns table
as
return (
 with x as
 (
  select
   sc.SetId,
   nullif(case
    when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then count(distinct ws.SkillName)
    when sc.SkillCombinator = 'OR' then count(distinct ws.SkillName)
   end, 0) as SkillsCount
  from
   dbo.SkillCombinator sc join
   dbo.RequiredSkills rs on rs.SetId = sc.SetId left join
   dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName
  group by
   sc.SetId, sc.SkillCombinator
 )
 select
  case
   when @Condition = 'AND' and count(SkillsCount) = (select count(*) from dbo.SkillCombinator) then 1
   when @Condition = 'OR' and count(SkillsCount) > 0 then 1
   else 0
  end as Result
 from
  x
);
0

, . , , , . , , , .

DECLARE @WorkerID       BIGINT  = 1,
        @LogicalCase    BIT     = 1,    --  [0 = OR], [1 = AND]
        @SkillScore     INT     = 0,
        @isTrue         BIT     = 0

SELECT @SkillScore  =   SUM (
                                CASE    WHEN SkillName = 'A' THEN 1
                                        WHEN SkillName = 'B' THEN 2
                                        WHEN SkillName = 'C' THEN 4
                                        WHEN SkillName = 'D' THEN 8
                                        WHEN SkillName = 'E' THEN 16
                                        WHEN SkillName = 'F' THEN 32
                                        WHEN SkillName = 'G' THEN 64
                                        WHEN SkillName = 'H' THEN 128
                                        WHEN SkillName = 'I' THEN 256
                                END
                            )
FROM WorkerSkills
WHERE WorkerID = @WorkerID


IF @LogicalCase = 0
BEGIN
    IF (@SkillScore & 7 = 7)                                                            --      set 1: A and B and C 
    OR ( (@SkillScore & 8 = 8) OR (@SkillScore & 16 = 16) OR (@SkillScore & 32 = 32) )  --  OR  set 2: D or E or F
    OR (@SkillScore & 448 = 448)                                                        --  OR  set 3: G and H and I 
    BEGIN   
        SET @isTrue = 1
    END
END
IF @LogicalCase = 1
BEGIN
    IF (@SkillScore & 7 = 7)                                                            --      set 1: A and B and C 
    AND ( (@SkillScore & 8 = 8) OR (@SkillScore & 16 = 16) OR (@SkillScore & 32 = 32) ) --  AND set 2: D or E or F
    AND (@SkillScore & 448 = 448)                                                       --  AND set 3: G and H and I 
    BEGIN   
        SET @isTrue = 1
    END
END


SELECT @isTrue
0

, :

declare @workerid int =5 -- input param
declare @setcombinator varchar(3) ='AND' -- input param

declare @skillsleft varchar(max)
declare @result varchar(10)
declare @getsets cursor
declare @set int

set @getsets = CURSOR FOR SELECT distinct SetId FROM SkillCombinator
OPEN @getsets
FETCH NEXT FROM @getsets INTO @set
-- set result by default
IF(@setcombinator='OR') set @result='FALSE' else set @result='TRUE'

WHILE @@FETCH_STATUS = 0
BEGIN -- compares each skillset against worker and determine match or mismatch

if (select skillcombinator from skillcombinator where setid=@set) = 'AND'
    BEGIN --needs all the skills in the set
        SET @skillsleft=
        (select count(*) from
        (select skillname from RequiredSkills a 
        join SkillCombinator b on a.setid=b.setid 
        where a.setid=@set
        except
        select skillname from workerskills where workerid=@workerid) t)
        -- override default depending on the logical combinations
        if (@skillsleft = 0 and @setcombinator='OR') set @result='TRUE' 
        if (@skillsleft > 0 and @setcombinator='AND')set @result='FALSE'
    END

if (select skillcombinator from skillcombinator where setid=@set) = 'OR'
    BEGIN --needs at least one of the skills in the set 
        SET @skillsleft=
        (select count(*) from
        (select skillname from RequiredSkills a 
        join SkillCombinator b on a.setid=b.setid 
        where a.setid=@set
        intersect
        select skillname from workerskills where workerid=@workerid) t)
        -- override default depending on the logical combinations
        if (@skillsleft > 0 and @setcombinator='OR') set @result='TRUE'
        if (@skillsleft = 0 and @setcombinator='AND') set @result='FALSE'
    END

FETCH NEXT FROM @getsets INTO @set
END
select @result
CLOSE @getsets
DEALLOCATE @getsets
0

Source: https://habr.com/ru/post/1523763/

More articles:


All Articles