February 7, 2018 at 6:25 pm
Is there a way in SSRS to convert a delimited list to a series of "AND EXISTS()" subqueries? In SSRS, it's really easy to use a multi-valued parameter with IN() to filter the results returned. The same result can be obtained using DelimitedSplit8K and IN(). But is it possible to OR the results instead without resorting to dynamic SQL? Here's a quick example (yes, I know the Candidate table isn't terribly realistic!)
CREATE TABLE Candidate(
CandidateID INT IDENTITY,
FirstName VARCHAR(15) NOT NULL
CONSTRAINT pkCandidate PRIMARY KEY (CandidateID),
CONSTRAINT uqFirstName UNIQUE (FirstName));
CREATE TABLE CandidateSkills(
SCandidateID INT,
Skill VARCHAR(10)
CONSTRAINT pkCS PRIMARY KEY (ScandidateID, Skill),
CONSTRAINT fkCandidate FOREIGN KEY (SCandidateID) REFERENCES Candidate(CandidateID));
INSERT INTO Candidate(FirstName) VALUES ('John'),('Jane'),('Robert');
INSERT INTO CandidateSkills(SCandidateID,Skill)
VALUES (1,'.Net'),(1,'C#'),(2,'C#'),(3,'BA');
I can get all Candidates with both @Skill1 and @Skill2, but ... what if I have an undetermined number of skills? (use a table-valued parameter?)
but how would I modify this to use EXISTS, because I want to AND the values together, and not OR them (which is what IN() does). Would I have to pass a delimited string, split it, and then build the series of EXISTS subqueries (meaning do a lot of dynamic SQL)?
DECLARE @Skill1 VARCHAR(10) = 'C#',
@Skill2 VARCHAR(10) = '.Net';
SELECT *
FROM Candidate c
WHERE EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill= @Skill1 )
AND EXISTS (
SELECT 1
FROM CandidateSkills cs
WHERE cs.SCandidateID = c.CandidateID
AND cs.Skill=@Skill2 )
February 7, 2018 at 6:46 pm
Exercised my Google-fu... Relational division, anybody?
https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list
February 7, 2018 at 8:35 pm
Here's an idea...
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Skill', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Skill;
CREATE TABLE tempdb.dbo.Skill (
skill_id INT NOT NULL IDENTITY(1,1),
skill_name VARCHAR(30) NOT NULL
CONSTRAINT pk_Skill PRIMARY KEY CLUSTERED (skill_id),
CONSTRAINT uq_Skill_skillname UNIQUE (skill_name)
);
INSERT tempdb.dbo.Skill (skill_name) VALUES ('C#'), ('.net'), ('C++'), ('T-SQL'), ('PL SQL');
END;
IF OBJECT_ID('tempdb.dbo.Candidate', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Candidate;
CREATE TABLE tempdb.dbo.Candidate (
candidate_id INT NOT NULL IDENTITY(1,1),
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
CONSTRAINT pk_Candidate PRIMARY KEY CLUSTERED (candidate_id)
);
INSERT tempdb.dbo.Candidate (first_name, last_name) VALUES
('Bob', 'Jones'), ('Mary', 'Baxter'), ('Kevin', 'Hall'), ('John', 'Doe'),
('Alison', 'Chains'), ('Molly', 'Hatchet'), ('Maxwell', 'House'), ('Red', 'Fox');
END;
IF OBJECT_ID('tempdb.dbo.CandidateSkills', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.CandidateSkills;
CREATE TABLE tempdb.dbo.CandidateSkills (
candidate_id INT NOT NULL,
skill_id INT NOT NULL,
proficiency_level INT NOT NULL
CONSTRAINT ck_CandidateSkills_proficiencylevel CHECK (proficiency_level BETWEEN 1 AND 5),
CONSTRAINT pk_CandidateSkills PRIMARY KEY CLUSTERED (candidate_id, skill_id)
);
INSERT tempdb.dbo.CandidateSkills (candidate_id, skill_id, proficiency_level) VALUES
(1, 1, 3), (1, 2, 2), (1, 5, 4), (2, 1, 4), (2, 3, 2), (2, 4, 1),
(3, 4, 5), (3, 1, 1), (4, 5, 5), (5, 1, 2), (5, 2, 2), (5, 3, 3),
(5, 4, 1), (5, 5, 2), (6, 2, 4), (6, 4, 4), (7, 1, 3), (7, 3, 3),
(6, 5, 2), (7, 5, 5), (8, 1, 4), (8, 4, 4);
END;
--SELECT * FROM dbo.Candidate c;
--SELECT * FROM dbo.Skill s;
--SELECT * FROM dbo.CandidateSkills cs;
--=======================================================================================
DECLARE
@skill1 INT = 1,
@skill2 INT = 2;
WITH
cte_required_skills AS (
SELECT
s.skill_id
FROM
( VALUES (@skill1), (@skill2) ) s (skill_id)
),
cte_cs_eval AS (
SELECT
cs.candidate_id,
cs.skill_id,
cs.proficiency_level,
skill_count = COUNT(1) OVER (PARTITION BY cs.candidate_id),
total_proficiency = SUM(cs.proficiency_level) OVER (PARTITION BY cs.candidate_id)
FROM
cte_required_skills rs
JOIN dbo.CandidateSkills cs
ON rs.skill_id = cs.skill_id
)
SELECT
c.candidate_id,
c.first_name,
c.last_name,
s.skill_name,
ce.proficiency_level
FROM
cte_cs_eval ce
JOIN dbo.Candidate c
ON ce.candidate_id = c.candidate_id
JOIN dbo.Skill s
ON ce.skill_id = s.skill_id
WHERE
ce.skill_count = 2 --<< number of required skills.
ORDER BY
ce.total_proficiency DESC;
February 8, 2018 at 10:57 am
Jason,
Wow. I'll take a while to get my head around all this. Say I'm using SSRS as a front end to this - Essentially "Show me all candidates that have this list of skills" In SSRS, I can make the skill list a multi-select, so how would that part work? I can get a count of items selected really easily, so I can pass a value instead of a hard-coded 2. Or is this now an SSRS question?
Thanks!
Pieter
February 8, 2018 at 12:17 pm
Basically this:
SELECT SCandidateID
FROM dbo.CandidateSkills
WHERE Skill IN (@ssrs_skill_param)
GROUP BY SCandidateID
HAVING COUNT(*) = <number_of_values_in_skill_param>
But not sure how in SSRS to get a count of the values passed in. I can't remember if SSRS gives you a variable/property that equates to count or not.
Worst case, I would think you'd be able to do something like below, getting the total from the Skills "master" table:
HAVING COUNT(*) = (SELECT COUNT(*) FROM dbo.Skills WHERE Skill IN (@ssrs_skill_param))
Of course you can't use the CandidateSkills for that part, because it's possible a skill would be entered that no Candidate had, and you don't want it to drop out of the total.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 8, 2018 at 12:24 pm
Oh, super handy! Thanks for asking... I was playing with the SSRS end of this... Getting the count of selected values is easy in SSRS. It's the passing that back to SQL Server that could be an issue.
return list of comma-separated values: JOIN(Parameters!Invoices.Value,",")
return a count of items selected: Parameters!Invoices.Count
Just wonder if I can pass them back to the stored procedure now.. (do I need a TVP?, or can I do it an easier way?)
February 8, 2018 at 2:10 pm
Maybe this is a simpler solution:
SELECT *
FROM dbo.Candidate AS c
WHERE c.candidate_id NOT IN(
SELECT c.candidate_id
FROM dbo.Candidate AS c
CROSS JOIN dbo.Skill AS s
LEFT JOIN dbo.CandidateSkills AS cs ON c.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE cs.candidate_id IS NULL);
It could be used with an additional condition to get specific skills
SELECT *
FROM dbo.Candidate AS c
WHERE NOT EXISTS(
SELECT 1
FROM dbo.Candidate AS ci
CROSS JOIN dbo.Skill AS s
LEFT JOIN dbo.CandidateSkills AS cs ON ci.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE cs.candidate_id IS NULL
AND ci.candidate_id = c.candidate_id
AND s.skill_id IN(1,2,4)); --This could use a multiple valued parameter from SSRS
February 8, 2018 at 3:57 pm
pietlinden - Thursday, February 8, 2018 10:56 AMJason,
Wow. I'll take a while to get my head around all this. Say I'm using SSRS as a front end to this - Essentially "Show me all candidates that have this list of skills" In SSRS, I can make the skill list a multi-select, so how would that part work? I can get a count of items selected really easily, so I can pass a value instead of a hard-coded 2. Or is this now an SSRS question?
Thanks!
Pieter
I was wandering about that... In any case, it only requires a minor change to the code (see below). In your OP you mentioned DelimitedSplit8K so I won't go into details about it or where to get it.
There are only 3, very simple,changes that need to be made...
#1 use a single CSV parameter rather than separate parameters.
#2 in the 1st CTE, split the SSV parameter with the help of DelimitedSplit8K.
#3 in the WHERE clause, use "LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1" to determine the number of elements in the CSV
... super easy...
USE tempdb;
GO
IF OBJECT_ID('tempdb.dbo.Skill', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Skill;
CREATE TABLE tempdb.dbo.Skill (
skill_id INT NOT NULL IDENTITY(1,1),
skill_name VARCHAR(30) NOT NULL
CONSTRAINT pk_Skill PRIMARY KEY CLUSTERED (skill_id),
CONSTRAINT uq_Skill_skillname UNIQUE (skill_name)
);
INSERT tempdb.dbo.Skill (skill_name) VALUES ('C#'), ('.net'), ('C++'), ('T-SQL'), ('PL SQL');
END;
IF OBJECT_ID('tempdb.dbo.Candidate', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.Candidate;
CREATE TABLE tempdb.dbo.Candidate (
candidate_id INT NOT NULL IDENTITY(1,1),
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
CONSTRAINT pk_Candidate PRIMARY KEY CLUSTERED (candidate_id)
);
INSERT tempdb.dbo.Candidate (first_name, last_name) VALUES
('Bob', 'Jones'), ('Mary', 'Baxter'), ('Kevin', 'Hall'), ('John', 'Doe'),
('Alison', 'Chains'), ('Molly', 'Hatchet'), ('Maxwell', 'House'), ('Red', 'Fox');
END;
IF OBJECT_ID('tempdb.dbo.CandidateSkills', 'U') IS NULL
BEGIN -- DROP TABLE tempdb.dbo.CandidateSkills;
CREATE TABLE tempdb.dbo.CandidateSkills (
candidate_id INT NOT NULL,
skill_id INT NOT NULL,
proficiency_level INT NOT NULL
CONSTRAINT ck_CandidateSkills_proficiencylevel CHECK (proficiency_level BETWEEN 1 AND 5),
CONSTRAINT pk_CandidateSkills PRIMARY KEY CLUSTERED (candidate_id, skill_id)
);
INSERT tempdb.dbo.CandidateSkills (candidate_id, skill_id, proficiency_level) VALUES
(1, 1, 3), (1, 2, 2), (1, 5, 4), (2, 1, 4), (2, 3, 2), (2, 4, 1),
(3, 4, 5), (3, 1, 1), (4, 5, 5), (5, 1, 2), (5, 2, 2), (5, 3, 3),
(5, 4, 1), (5, 5, 2), (6, 2, 4), (6, 4, 4), (7, 1, 3), (7, 3, 3),
(6, 5, 2), (7, 5, 5), (8, 1, 4), (8, 4, 4);
END;
--SELECT * FROM dbo.Candidate c;
--SELECT * FROM dbo.Skill s;
--SELECT * FROM dbo.CandidateSkills cs;
--=======================================================================================
DECLARE
@_mv_skill_param VARCHAR(8000) = '1,3,4'; --<< using a single CSV parameter... compatible with SSRS MV parameter output.
WITH
cte_required_skills AS (
SELECT
skill_id = ds.Item
FROM
dbo.DelimitedSplit8K(@_mv_skill_param, ',') ds
),
cte_cs_eval AS (
SELECT
cs.candidate_id,
cs.skill_id,
cs.proficiency_level,
skill_count = COUNT(1) OVER (PARTITION BY cs.candidate_id),
total_proficiency = SUM(cs.proficiency_level) OVER (PARTITION BY cs.candidate_id)
FROM
cte_required_skills rs
JOIN dbo.CandidateSkills cs
ON rs.skill_id = cs.skill_id
)
SELECT
c.candidate_id,
c.first_name,
c.last_name,
s.skill_name,
ce.proficiency_level
FROM
cte_cs_eval ce
JOIN dbo.Candidate c
ON ce.candidate_id = c.candidate_id
JOIN dbo.Skill s
ON ce.skill_id = s.skill_id
WHERE
ce.skill_count = LEN(@_mv_skill_param) - LEN(REPLACE(@_mv_skill_param, ',', '')) + 1 --<< counts the delimiters and then adds 1
ORDER BY
ce.total_proficiency DESC;
HTH,
Jason
February 8, 2018 at 4:40 pm
Super cool, Luis! Many graces!
This worked:SELECT candidate_id, first_name, last_name
FROM Candidate AS c
WHERE (NOT EXISTS
(SELECT 1 AS Expr1
FROM Candidate AS ci CROSS JOIN
Skill AS s LEFT OUTER JOIN
CandidateSkills AS cs ON ci.candidate_id = cs.candidate_id AND s.skill_id = cs.skill_id
WHERE (cs.candidate_id IS NULL) AND (ci.candidate_id = c.candidate_id) AND (s.skill_id IN (@prmSkills))))
Then the parameter, @prmSkills is multi-valued - SELECT skill_id, skill_name
FROM Skill
ORDER BY skill_name
Works a champ!
February 10, 2018 at 1:03 pm
pietlinden - Wednesday, February 7, 2018 6:46 PMExercised my Google-fu... Relational division, anybody?
https://stackoverflow.com/questions/15977126/select-rows-that-match-all-items-in-a-list
http://publications.sqltopia.com/Relational%20Division.pdf
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply