UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Multiple Criteria, SQL Server 2008 R2    
 
   
WildBird
post Jul 14 2017, 01:40 AM
Post#1


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


Hi All,

Having a brain freeze, again!

I have some data,
People table
Bill Smith
John Jones

And
Resources table
ABC
DEF
HIJ


Bill Smith has ABC and DEF
John Jones has ABC and HIJ


If I pass a parameter of 'ABC', it will return Bill Smith and John Jones

The issue I have is I need to be able to get the people who have ALL the parameters passed in, so if parameters are ABC, DEF, it needs to return Bill Smith only as John Jones doesnt have all the criteria.

This is obviously test data, and real life will have lots more records, and criteria count could be 1, or it might be 10 or more. The criteria will be a string, so something like
'ABC'
'ABC, DEF'
'ABC, HIJ, LMN'
'DEF'
I can split the data out in a function, but just brain stopped working for rest.

This is SQL Server 2008 R2.

Thanks in advance!

Cheers,

Coop

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
ranman256
post Jul 14 2017, 04:34 AM
Post#2



Posts: 768
Joined: 25-April 14



you need a form, the user would pick the items in tResources.
but then to create criteria ,you may need VB to create the SQL. (its easy in Query design, but harder in SQL)

in query design:
bring the tResources table into the query , repeat 1 for every item
so your example , there would be 2 tResources
tResources and tResources1

join them on ID
bring down 1 ResourceName from each table :

select tResources.ID, tResources.ResourceName , tResources1.ResourceName

under criieria, each table gets 1 of the 2 criteria
WHERE tResources.ResourceName= 'abc'
AND tResources1.ResourceName= 'def'

(photo attached)
Attached File  sql_AND.jpg ( 21.44K )Number of downloads: 1

This post has been edited by ranman256: Jul 14 2017, 04:34 AM
Go to the top of the page
 
MadPiet
post Jul 14 2017, 05:27 AM
Post#3



Posts: 2,184
Joined: 27-February 09



Wildbird,

That was just flat out mean. Not pretty, but this is what I came up with. I'm using a splitter function that Jeff Moden wrote (converts a delimited list into a table)...

CODE
use tempdb;
go

CREATE TABLE Person(
    PersonID INT IDENTITY,
    FirstName VARCHAR(10),
    LastName VARCHAR(10)
CONSTRAINT pkPerson PRIMARY KEY (PersonID));

CREATE TABLE Resources(
    ResourceID CHAR(3) PRIMARY KEY
);

CREATE TABLE PR (
    PersonID INT,
    ResourceID CHAR(3)
CONSTRAINT pkHas PRIMARY KEY (PersonID, ResourceID)
CONSTRAINT fkPersonID FOREIGN KEY (PersonID) REFERENCES Person(PersonID));
GO

INSERT INTO Person(FirstName, LastName) VALUES ('Bill', 'Smith'),('John','Jones');
INSERT INTO Resources (ResourceID) VALUES ('ABC'),('DEF'),('HIJ');
INSERT INTO PR (PersonID, ResourceID) VALUES (1,'ABC'),(1,'DEF'),(2,'ABC'), (2,'HIJ'), (1,'HIJ');
-- both Bill and John have (ABC, HIJ).

DECLARE    @ResourceList VARCHAR(15) = 'ABC,DEF'

-- show all people with all skills in list @ResourceList

SELECT *
FROM Person px
WHERE px.PersonID NOT IN (  -- missing a "course"
                            SELECT PersonID
                            FROM (
                                    SELECT p.PersonID
                                        , split.Item
                                        , pr.ResourceID
                                    FROM Person p
                                        CROSS APPLY Scratchpad.dbo.DelimitedSplit8K(@ResourceList,',') split
                            LEFT JOIN dbo.PR ON pr.PersonID = p.PersonID AND pr.ResourceID = split.Item
) x
WHERE ResourceID IS NULL );


Oh, right... the function for DelimitedSplit8K...



CODE
CREATE FUNCTION [dbo].[DelimitedSplit8K]

-- Jeff Moden wrote this... I didn't... see the link for the explanation of the whole thing

--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;
GO




Go to the top of the page
 
MadPiet
post Jul 14 2017, 05:29 AM
Post#4



Posts: 2,184
Joined: 27-February 09



RanMan,

It's a SQL Server question. If you tried to run a VBA function on a SQL Server table, it would drag the entire table across the network and try to process it on your local machine. NOT pretty.
Go to the top of the page
 
MadPiet
post Jul 14 2017, 10:01 PM
Post#5



Posts: 2,184
Joined: 27-February 09



Here's the version that Jacob Wilkins wrote... actually 2 of them:
(I just can't remember when CROSS APPLY was added...
v1:
CODE
SELECT p.PersonID, p.FirstName, p.LastName
FROM  Person P
  CROSS APPLY
  DelimitedSplit8K(@ResourceList,',') split
  LEFT JOIN
  dbo.PR ON PR.PersonID=P.PersonID AND pr.ResourceID=split.Item
GROUP BY  p.PersonID, p.FirstName, p.LastName
HAVING  COUNT(pr.ResourceID)=COUNT(split.Item)
OPTION (RECOMPILE);


v2:
CODE
SELECT p.*
FROM Person P
WHERE NOT EXISTS (SELECT P.PersonID, Resource_ID=Item FROM DelimitedSplit8K(@ResourceList,',') split
  EXCEPT
  SELECT P.PersonID, PR.ResourceID FROM PR WHERE PR.PersonID=P.PersonID);
Go to the top of the page
 
WildBird
post Jul 16 2017, 05:40 PM
Post#6


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


Cheers guys!

@MadPiet, I actually saw the Jeff Moden splitter, and have a similar one. They are small strings, less than 10 characters. Anyway, I have that bit sorted I think.

Don't have internet at home, and today cant get access to the SQL Server I need so might be a little while before I can get onto it and test these out. Will let you know.

Cheers!

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jul 16 2017, 07:27 PM
Post#7


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


MadPiet,

I could buy you a beer! Looks like it should work. Have a few tweaks and fix table names etc, but concept, which is what I was after basically, means it should be doable.

In case anyone is wondering, it is a roster scheduling system - managers select a 'resource' - a person, who has a skill or skills. Originally we had them select one skill per line (this is still in sort of proof of concept stage - so final go ahead and design hasn't been done yet, so things changing a bit as we go). Business has said they need to be able to select resources with multiple skills, i.e someone needs to be a licenced driver, operator, welder etc. Having code like this means it will match those with all the skills.

Cheers!

Coop


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jul 16 2017, 07:54 PM
Post#8



Posts: 2,184
Joined: 27-February 09



Coop,

You should create an account on SQLServerCentral.com - there are some scary smart people there... Tons of articles, lots of sick smart people... no junk mail.
Here's the whole discussion:
https://www.sqlservercentral.com/Forums/188...[censored]-in-a-list
This post has been edited by MadPiet: Jul 16 2017, 08:18 PM
Go to the top of the page
 
WildBird
post Jul 16 2017, 08:32 PM
Post#9


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


Have been a member on SQLServerCentral for years now. I have a bit of a read at times, if I have time, and often end up there when researching questions. I have just found I get better and faster answers here than anywhere else though :-) They do have some very smart people on there though, that's for sure!

Anyway, its Monday here and having a few bits of 'fun' with this code. Have added extra records, and is having issues with returning anything if they have ALL the records, and leading spaces.

Its sort of messy here, I am logged on as a different user (I don't have access to the server yet), cant add anything to tempdb, and other things such as schemas etc - so I have had to tweak things here and there and bit of a hassle to replicate.

I am pretty sure it is because its Monday!..... Will see how I go getting it to work.

Cheers




--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
WildBird
post Jul 16 2017, 09:19 PM
Post#10


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


Aaaargh! Just couldn't see the issue right in front of me! Had DECLARE @ResourceList VARCHAR(15)

I was using multiple criteria that pushed the length out past 15.
DECLARE @ResourceList VARCHAR(15) = 'ABC,DEF, HIJ,KLM'

select REPLACE(@ResourceList,' ','')

As soon as I saw this, I knew what the issue was. Mondays........

Now to tidy up a bit and more testing, but much happier than Friday when I left work.

Cheers

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jul 16 2017, 09:28 PM
Post#11



Posts: 2,184
Joined: 27-February 09



Wonder if this wouldn't be better as a table-valued function... then you'd just use CROSS APPLY to join it to your Employees table or whatever.
This post has been edited by MadPiet: Jul 16 2017, 10:21 PM
Go to the top of the page
 
WildBird
post Jul 16 2017, 11:00 PM
Post#12


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


1 thing I noticed, the Split function doesn't trim the values. I guess it comes down to the data going in, and it is a string 'ABC,DEF, HIJ' will return
ABC
DEF
HIJ
Just need to be aware of this I guess for anyone else using this in the future. I will deal with it somewhere to make sure they are handled as expected (i.e. trimmed)



--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Jul 17 2017, 07:48 AM
Post#13



Posts: 2,184
Joined: 27-February 09



WildBird,

Tweak your input parameter value inside the code.

SET @Param = REPLACE(@Param,', ',',')

Then you should be all set.

Duh, that's what you just said! It's not 8 AM yet here... clearly I'm not fully awake!
This post has been edited by MadPiet: Jul 17 2017, 07:52 AM
Go to the top of the page
 
WildBird
post Jul 19 2017, 02:34 AM
Post#14


UtterAccess VIP
Posts: 3,266
Joined: 19-August 03
From: Perth, Australia


Been off at training sessions and other fun stuff, but got back to this today. Looks to be working, but might have a few minor issues. Will let you know if any issues and post here to let anyone else who finds this thread in the future.


--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st October 2017 - 01:24 AM