October 7, 2016 at 10:17 am
Hi Guys
I might struggle to explain this so please bear with me. We have a system that records Person and a set of up to 6 indicators against them (IDs 1 to 6). These are recorded in Person and PersonIndicator tables.
I have a requirement for a query to sit behind a SSRS report and the user need to be able to select a set of indicator from a multi valued drop down. The report should return any Persons that have ALL of the indicators selected. I'm trying to find a sensible way of achieving that.
Here's what I have so far. It works but it seems fudgy and I can't help feeling there's a better way:-
Create Table #Person
(PersonID int, Name nvarchar(50))
Create Table #PersonIndicator
(PersonID int, Indicator int)
Insert into #Person Values (1, 'Funky')
Insert into #PersonIndicator Values (1,1)
Insert Into #PersonIndicator Values (1,3)
Insert into #PersonIndicator Values (1,5)
Insert into #Person Values (2, 'Mary')
Insert into #PersonIndicator Values (2,1)
Insert Into #PersonIndicator Values (2,2)
Insert into #PersonIndicator Values (2,5)
Insert into #PersonIndicator Values (2,6)
Declare @IndList nvarchar(50)='1,2'
Select *
From #Person P
Where (Select COUNT(*)
From #PersonIndicator PIn
Join Warehouse.fnSplit(@IndList, ',') IL
on PIn.Indicator = IL.Data
Where PIn.PersonID = P.PersonID)
=
(select COUNT(*)
From Warehouse.fnSplit(@IndList, ','))
Drop Table #Person
Drop Table #PersonIndicator
That's returning Mary because she has both 1 and 2. It's not returning Funky because he doesn't have indicator 2. Mary does not get eliminated for also having 5 and 6, it's enough that she has 1 and 2 and any extras are irrelevant. I hope that explains things well but please feel free to ask for clarification is I've been unclear.
Does anyone have any suggestions on a better, more readable approach to this? Doing it based on counts seems obtuse to me.
October 7, 2016 at 10:46 am
Here's an alternative, though I'm not sure it's any better:
IF OBJECT_ID('tempdb..#Person', 'U') IS NOT NULL
DROP TABLE #Person;
CREATE TABLE #Person
(
PersonID INT
,Name NVARCHAR(50)
);
IF OBJECT_ID('tempdb..#PersonIndicator', 'U') IS NOT NULL
DROP TABLE #PersonIndicator;
CREATE TABLE #PersonIndicator
(
PersonID INT
,Indicator INT
);
IF OBJECT_ID('tempdb..#IndList', 'U') IS NOT NULL
DROP TABLE #IndList;
CREATE TABLE #IndList
(
Indicator INT PRIMARY KEY CLUSTERED
);
INSERT #Person
VALUES (1, 'Funky');
INSERT #PersonIndicator
VALUES (1, 1);
INSERT #PersonIndicator
VALUES (1, 3);
INSERT #PersonIndicator
VALUES (1, 5);
INSERT #Person
VALUES (2, 'Mary');
INSERT #PersonIndicator
VALUES (2, 1);
INSERT #PersonIndicator
VALUES (2, 2);
INSERT #PersonIndicator
VALUES (2, 5);
INSERT #PersonIndicator
VALUES (2, 6);
DECLARE @IndList NVARCHAR(50)= '1,2';
INSERT #IndList
(
Indicator
)
SELECT CAST(udsk.Item AS INT)
FROM dbo.udfDelimitedSplit8K(@IndList, ',') udsk;
DECLARE @IndCount INT = @@ROWCOUNT;
SELECT p.PersonID
, p.Name
FROM #Person p
JOIN #PersonIndicator pi ON p.PersonID = pi.PersonID
JOIN #IndList il ON pi.Indicator = il.Indicator
GROUP BY p.PersonID
, p.Name
HAVING COUNT(1) = @IndCount;
Edit: removed unwanted @ sign.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 7, 2016 at 11:41 am
LOL, thanks. This was a test for someone, but glad an answer came through.
October 10, 2016 at 1:31 am
Thanks for posting this, Steve, and thanks for answering, Phil. It may be in the test section but I'll call getting an answer a win:-D
I think that's basically the same approach as mine, though broken down a bit more. Somehow counting the indicators feels a little flakey. Logically I know it's not because you're counting the elements from a set that exist in another and you can work with distinct to make sure you're covering all the values but I sort of feel I should be comparing that actual values rather than the counts. I'm coming up blank myself though so I think counts will do for now.
Thanks again to you both.
October 12, 2016 at 12:00 pm
I did something similar, though I think I used temp tables and counts to make sure that they matched and the counts were the same for the matches. It wasn't very pretty code as it had to deal with a _bunch_ of things to look consider, but it worked.
October 13, 2016 at 10:19 am
Yeah, it seems like all roads lead to counting so that's what I've gone with for now. Thanks for your thoughts. It's good to know I'm not missing anything obvious.:-)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply