Solved

sql query with a where clause

Posted on 2015-01-28
34
70 Views
Last Modified: 2015-01-29
I have a query that works. Now I also have  filter that called hub. The hub is location which if comprised of other locations.

For example, I want to say show me all the session within hub A (have location 1,location 2, location3).

The session have a table column  locationKeyList which the is the list (1,2,3)

how can I construct my query where I can say
session.locationkeyList = values of the hub A (1,2,3)

Because I may pass a multiple hubs values to the query . Set of locations identify the hub
0
Comment
Question by:erikTsomik
  • 17
  • 10
  • 3
  • +2
34 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40575943
Give us a data mockup, both current data and expected return set, of what you're trying to pull off here.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40575946
you need to build the dynamic sql string that eventually looks like this

where session.locationkeyList  in (1,2,3)
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40575982
Aneesh I just try that but it does not work
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40575988
the query is here

DECLARE @sessionStart DATETIME,
				@sessionEnd DATETIME,
				@instructorKey INT,
				@locationKey INT

			SET @instructorKey = <cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.userKey#" />
			SET @sessionStart = <cfqueryparam cfsqltype="cf_sql_date" value="#realStart#" null="#IIf(IsDate(realStart), DE("no"), DE("yes"))#" />
			SET @sessionEnd = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#realEnd#" null="#IIf(IsDate(realEnd), DE("no"), DE("yes"))#" />
			SET @locationKey = <cfif isDefined("form.locationkey") and form.locationkey neq ""><cfqueryparam cfsqltype="cf_sql_integer" value="#FORM.locationKey#" null="#IIf(IsNumeric(FORM.locationKey), DE("no"), DE("yes"))#" /><cfelse>null</cfif>

			SELECT SU.sessionUnitKey,
				SU.sessionStart,
				SU.sessionEnd,
				SU.unit,
				SU.unitOrder,
				L.locationKey,
				L.name AS LocationName,
				S.status,
				S.sessionKey,
				
				
			    U.userKey,
			    U.firstName,
			    U.lastName,
			    PT.productTypeCode AS sessionType, s.ClassRoom, s.locationkeylist
			FROM sessionUnit SU WITH (NOLOCK)
				INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey
				left JOIN location L WITH (NOLOCK) ON (S.locationKey = L.locationKey <!---OR convert(varchar,S.locationKeyList) = '#locHubLocations#'--->)
				INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey
			    INNER JOIN users U WITH (NOLOCK) ON SU.instructorKey = U.userKey
			    INNER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
			WHERE
			1=1
			



			<cfif len(locLocation)>





					AND (S.locationKey in (<cfqueryparam cfsqltype="cf_sql_integer" value="#locLocation#" list="true">) OR
					S.locationKeyList in (<cfqueryparam cfsqltype="cf_sql_varchar" value="#locLocation#" list="true">)
				)





			</cfif>


				AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart)
				AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd)
				

			ORDER BY SU.sessionStart

Open in new window

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40575996
erikTsomik - For future reference please give us an error message instead of saying 'it does not work', which doesn't tell us anything meaningful to solve your question.

A proper analogy would be to bring your car to the shop and say to the repairman 'it does not work', and see how that goes over.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576012
For example the locationKeyList column lokks like this 15,10,34,102,260. I know it is bad design but that what I got

and the hobLocatiobs is equal  15,10,34,102,260

By doing S.locationKeyList in ( 15,10,34,102,260) does not work
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576044
here is the query with values
DECLARE @sessionStart DATETIME,
@sessionEnd DATETIME,
@instructorKey INT,
@locationKey INT

SET @instructorKey = 42
SET @sessionStart = '2015-03-01'
SET @sessionEnd = '2015-04-05'
SET @locationKey = null
SELECT SU.sessionUnitKey, SU.sessionStart, SU.sessionEnd, SU.unit, SU.unitOrder, L.locationKey, L.name AS LocationName, S.status, S.sessionKey,
 U.userKey, U.firstName, U.lastName, PT.productTypeCode AS sessionType, s.ClassRoom, s.locationkeylist
 FROM sessionUnit SU WITH (NOLOCK)
 INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey
 left JOIN location L WITH (NOLOCK) ON (S.locationKey = L.locationKey )
 INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey
 INNER JOIN users U WITH (NOLOCK) ON SU.instructorKey = U.userKey
 INNER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
 WHERE 1=1
 AND EXISTS ( SELECT 1 from ins_car_loc ICL WITH (NOLOCK) WHERE ICL.RptingInskey = SU.instructorKey and ICL.instructorKey = @instructorKey and ICL.endDt is NULL )
 AND (S.locationKey in (10,260,34,102,15) OR convert(varchar,S.locationKeyList) in (10,260,34,102,15) )
 AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd) ORDER BY SU.sessionStart
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576054
It is really necessary for you to remember that the field only LOOKS LIKE a series of IDs it is actually ONE LONG STRING

1234,5678,7532,9631 is actually '1234,5678,7532,9631'

it is NOT

1234
5678
7532
9631

The other thing to remember is that IN () is just a shortcut for OR, like this

where ID IN (1234,5678,7532,9631)
is the equivalent of:
where ( ID = 1234 OR ID = 5678 OR ID = 7532 OR ID = 9631 )

Taking both these points together is that you could end up with something like this in your query:

where ( ID = '1234,5678,7532,9631' )

and because there simply is no ID that equals a string of "1234,5678,7532,9631" it won't work.

The suggestion above to use dynamic sql should overcome these problems.
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40576056
>>> OR convert(varchar,S.locationKeyList) in (10,260,34,102,15)  
  change it to  
                    OR convert(varchar (10) ,S.locationKeyList) in (10,260,34,102,15)

Also I don't really get that logic. Can you post some sample values of S.locationKeyList column
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576059
sorry, I posted without seeing the full query.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576074
OR CONVERT(varchar, S.locationKeyList) IN (10, 260, 34, 102, 15))


S.locationKeyList MUST already be a string data type (varchar or nvarchar) so why convert it to varchar?

A string field '10,260,34,102,15' does not equal 10  
A string field '10,260,34,102,15' does not equal 260  
A string field '10,260,34,102,15' does not equal 102
A string field '10,260,34,102,15' does not equal 15  


A string field '10,260,34,102,15' would equal '10,260,34,102,15'
but would NOT equal '10, 260, 34, 102, 15'
or would NOT equal '26, 10,260,34,102,15'
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40576164
Use a table-based function that will split the list into a table with one row per value.  Then check to see if s.locationKey exists in that table.  Here's sample code, using a highly-efficient splitter
function named "DelimitedSplit8K", the code for which follows the query.

...
  AND EXISTS(
      SELECT 1 FROM dbo.DelimitedSplit8K('10,260,34,102,15', ',') ds WHERE ds.Item = s.locationKey
      )
  AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd)
ORDER BY SU.sessionStart
...


SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--NOTE: (max) data type will at least double the time to do the split!
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

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576326
ScottPletcher. Just ran the code and get no records back
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576331
DECLARE @sessionStart DATETIME,
@sessionEnd DATETIME,
@instructorKey INT,
@locationKey INT

SET @instructorKey = 42
SET @sessionStart = '2015-03-01'
SET @sessionEnd = '2015-04-05'
SET @locationKey = null
SELECT SU.sessionUnitKey, SU.sessionStart, SU.sessionEnd, SU.unit, SU.unitOrder, L.locationKey, L.name AS LocationName, S.status, S.sessionKey,
 U.userKey, U.firstName, U.lastName, PT.productTypeCode AS sessionType, s.ClassRoom, s.locationkeylist
 FROM sessionUnit SU WITH (NOLOCK)
 INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey
 left JOIN location L WITH (NOLOCK) ON (S.locationKey = L.locationKey )
 INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey
 INNER JOIN users U WITH (NOLOCK) ON SU.instructorKey = U.userKey
 INNER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
 WHERE 1=1
 AND EXISTS ( SELECT 1 from ins_car_loc ICL WITH (NOLOCK) WHERE ICL.RptingInskey = SU.instructorKey and ICL.instructorKey = @instructorKey and ICL.endDt is NULL )
 
  AND EXISTS(
      SELECT 1 FROM DelimitedSplit8K('15,10,34,102,260', ',') ds WHERE ds.Item = s.locationKeyList
      )

 AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd) ORDER BY SU.sessionStart
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576346
the function produce 5 separate rows and then you saying equal to the list I think that may be an issue
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576443
I agree; it WILL be an issue, but as you haven't supplied any sample data we have to guess what would make sense.

Please try:

  AND EXISTS(
      SELECT 1 FROM DelimitedSplit8K('15,10,34,102,260', ',') ds WHERE ds.Item = S.locationKey
      )


but I am assuming S.locationKey is the correct field to use.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576610
The correct field is s.locationkeylist and it looks like the from my previous post is a comma delimited list of location ids
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576691
*sigh*

the field s.locationkeylist contains ONE STRING. That string LOOKS LIKE a set of IDs (but it is not a set of IDs).

Scott has used a function to divide that list of IDs into separate rows so that each part of the list can be evaluated as an ID

e.g.

the list = '1234,5678,9012'

becomes 3 rows after using the function:
1234
5678
9012

So there is no point in comparing each of those rows to the list that generated them, which would look like this:

1234 <> '1234,5678,9012'
5678 <> '1234,5678,9012'
9012 <> '1234,5678,9012'


s.locationkeylist appears to be a listing of values that one should find in s.locationkey

So after using the function against s.locationkeylist compare those rows to s.locationkey
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40576695
OR because I don't know the data perhaps the correct field is L.locationkey

I do wish you would provide some sample data
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576731
how would I handle the situation if I have 2 hubs which will be  a different set of lists
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576904
is that possible to say some thing like this
EXISTS(
                                          SELECT 1 FROM DelimitedSplit8K('#locHubLocations#', ',') ds
                                            inner join Location L2 on L2.locationKey = ds.Item
                                          where  L2.locationKey in (#locHubLocations#)
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40576905
DECLARE @sessionStart DATETIME, 
@sessionEnd DATETIME, 
@instructorKey INT, 
@locationKey INT 
SET @instructorKey = 42 
SET @sessionStart = '2015-02-01'
SET @sessionEnd = '2015-03-01'
 SET @locationKey = null
  SELECT SU.sessionUnitKey, SU.sessionStart, SU.sessionEnd, SU.unit, SU.unitOrder, L.locationKey, L.name AS LocationName, S.status, S.sessionKey, 
 U.userKey, U.firstName, U.lastName, PT.productTypeCode AS sessionType, s.ClassRoom, s.locationkeylist
  FROM sessionUnit SU WITH (NOLOCK) 
  INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey 
  left JOIN location L WITH (NOLOCK) ON (S.locationKey = L.locationKey ) 
  INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey 
  INNER JOIN users U WITH (NOLOCK) ON SU.instructorKey = U.userKey 
  INNER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey
   WHERE 1=1
    AND EXISTS ( SELECT 1 from ins_car_loc ICL WITH (NOLOCK) WHERE ICL.RptingInskey = SU.instructorKey and ICL.instructorKey = @instructorKey and ICL.endDt is NULL ) 
	AND EXISTS( SELECT 1 FROM DelimitedSplit8K('10,260,34,102,15', ',') ds inner join Location L2 on ds.Item = L2.locationKey ) 
	AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd) ORDER BY SU.sessionStart

Open in new window


and data sample
sessionUnitKey	sessionStart	sessionEnd	unit	unitOrder	locationKey	LocationName	status	sessionKey	userKey	firstName	lastName	sessionType	ClassRoom	locationkeylist
171933	2015-02-01 14:30:00.000	2015-02-01 16:30:00.000	NULL	NULL	4	Blair High School - AM Class	enabled	155690	13783	Richard	Burge	BW	NULL	4,5
172332	2015-02-02 06:00:00.000	2015-02-02 07:40:00.000	NULL	NULL	NULL	NULL	enabled	155808	42	Christine	Porter	BW	NULL	192,223
172333	2015-02-02 07:45:00.000	2015-02-02 09:25:00.000	NULL	NULL	NULL	NULL	enabled	155809	42	Christine	Porter	BW	NULL	192,223
172334	2015-02-02 09:30:00.000	2015-02-02 11:10:00.000	NULL	NULL	NULL	NULL	enabled	155810	42	Christine	Porter	BW	NULL	192,223
172335	2015-02-02 11:15:00.000	2015-02-02 13:15:00.000	NULL	NULL	NULL	NULL	enabled	155811	42	Christine	Porter	BW	NULL	15,10,34,102,260
172336	2015-02-02 13:15:00.000	2015-02-02 15:15:00.000	NULL	NULL	NULL	NULL	enabled	155812	42	Christine	Porter	BW	NULL	15,10,34,102,260
172337	2015-02-02 15:30:00.000	2015-02-02 17:30:00.000	NULL	NULL	NULL	NULL	enabled	155813	42	Christine	Porter	BW	NULL	15,10,34,102,260
172338	2015-02-02 17:30:00.000	2015-02-02 19:30:00.000	NULL	NULL	NULL	NULL	enabled	155814	42	Christine	Porter	BW	NULL	15,10,34,102,260
172339	2015-02-02 19:45:00.000	2015-02-02 21:45:00.000	NULL	NULL	NULL	NULL	enabled	155815	42	Christine	Porter	BW	NULL	15,10,34,102,260
172340	2015-02-03 06:00:00.000	2015-02-03 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155816	42	Christine	Porter	BW	NULL	15,10,34,102,260
171329	2015-02-05 07:15:00.000	2015-02-05 09:15:00.000	NULL	NULL	9	Olney - Fair Hill Shopping Center	enabled	155530	2645	Michael	Ryan	BW	NULL	NULL
171330	2015-02-05 09:15:00.000	2015-02-05 11:15:00.000	NULL	NULL	34	Potomac Woods Shopping Center	enabled	155531	2645	Michael	Ryan	BW	NULL	NULL
172266	2015-02-10 06:00:00.000	2015-02-10 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155778	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172363	2015-02-10 06:00:00.000	2015-02-10 07:40:00.000	NULL	NULL	NULL	NULL	enabled	155841	42	Christine	Porter	BW	NULL	1,3,9,171,205,185
172267	2015-02-10 08:00:00.000	2015-02-10 10:00:00.000	NULL	NULL	NULL	NULL	enabled	155779	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172268	2015-02-10 14:00:00.000	2015-02-10 16:00:00.000	NULL	NULL	NULL	NULL	enabled	155780	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172269	2015-02-10 16:00:00.000	2015-02-10 18:00:00.000	NULL	NULL	NULL	NULL	enabled	155781	13783	Richard	Burge	BW	NULL	15,10,34,102,260
171332	2015-02-10 17:30:00.000	2015-02-10 19:30:00.000	NULL	NULL	23	Germantown Regal Hoyts Cinemas BTW	enabled	155533	10264	Jay	VanGilder	BW	NULL	NULL
172348	2015-02-11 06:00:00.000	2015-02-11 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155824	42	Christine	Porter	BW	NULL	15,10,34,102,260
172349	2015-02-12 06:00:00.000	2015-02-12 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155825	42	Christine	Porter	BW	NULL	15,10,34,102,260
172350	2015-02-12 08:00:00.000	2015-02-12 10:00:00.000	NULL	NULL	NULL	NULL	enabled	155826	42	Christine	Porter	BW	NULL	15,10,34,102,260
172274	2015-02-15 06:00:00.000	2015-02-15 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155786	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172275	2015-02-15 08:00:00.000	2015-02-15 10:00:00.000	NULL	NULL	NULL	NULL	enabled	155787	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172278	2015-02-16 06:00:00.000	2015-02-16 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155790	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172276	2015-02-17 06:00:00.000	2015-02-17 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155788	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172277	2015-02-17 13:15:00.000	2015-02-17 15:15:00.000	NULL	NULL	NULL	NULL	enabled	155789	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172271	2015-02-18 06:00:00.000	2015-02-18 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155783	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172352	2015-02-18 06:00:00.000	2015-02-18 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155828	42	Christine	Porter	BW	NULL	15,10,34,102,260
172272	2015-02-18 08:00:00.000	2015-02-18 10:00:00.000	NULL	NULL	NULL	NULL	enabled	155784	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172273	2015-02-18 12:30:00.000	2015-02-18 14:30:00.000	NULL	NULL	NULL	NULL	enabled	155785	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172347	2015-02-19 06:00:00.000	2015-02-19 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155823	42	Christine	Porter	BW	NULL	15,10,34,102,260
172261	2015-02-20 06:00:00.000	2015-02-20 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155773	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172263	2015-02-20 08:00:00.000	2015-02-20 10:00:00.000	NULL	NULL	NULL	NULL	enabled	155775	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172262	2015-02-20 10:00:00.000	2015-02-20 12:00:00.000	NULL	NULL	NULL	NULL	enabled	155774	13783	Richard	Burge	BW	NULL	15,10,34,102,260
172270	2015-02-20 12:00:00.000	2015-02-20 14:00:00.000	NULL	NULL	NULL	NULL	enabled	155782	13783	Richard	Burge	BW	NULL	15,10,34,102,260
171976	2015-02-28 06:00:00.000	2015-02-28 08:00:00.000	NULL	NULL	NULL	NULL	enabled	155728	10	Neal	Branthover	BW	NULL	15,10,34,102,260

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40577535
are there any update on this. Please advise.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40577557
>> how would I handle the situation if I have 2 hubs which will be  a different set of lists <<

My code uses the list from the column name you gave:
s[ession].locationkeyList

The code will split the list into a table with a single column with each value, then check to see if the value you are trying to match EXISTS in the table.

Each row can have its own custom list, or one list can match others.

The CROSS APPLY will do this using existing columns from the existing query -- you do not need to, and do not want to, refer to tables in this code.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40577775
Can you show me a cross apply. I never used cross apply before.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40577800
CORRECTION:

Sorry, I didn't need, and thus didn't use, a CROSS APPLY in the code above.

But the table-valued function will be re-analyzed for each row, so that every list of values will get evaluated separately as the row is read (it is possible that SQL will cache previous results and be able to do a lookup rather than actually re-execute the function, but the row's data is still being evaluated to give a separate result for that row).
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40578166
I try using the CROSS APPLY but does not seems to work

Here the query
DECLARE @sessionStart DATETIME, 
@sessionEnd DATETIME, 
@instructorKey INT, 
@locationKey INT 

SET @instructorKey = 42	
SET @sessionStart = '2015-03-01'
SET @sessionEnd = '2015-04-05'
SET @locationKey = null 
SELECT SU.sessionUnitKey, s.locationKey,s.locationKeyList,SU.sessionStart, SU.sessionEnd
FROM sessionUnit SU WITH (NOLOCK) 
INNER JOIN session S WITH (NOLOCK) ON SU.sessionKey = S.sessionKey 
left JOIN location L WITH (NOLOCK) ON (S.locationKey = L.locationKey ) 
INNER JOIN product P WITH (NOLOCK) ON S.productKey = P.productKey 
INNER JOIN users U WITH (NOLOCK) ON SU.instructorKey = U.userKey 
INNER JOIN lkup_productType PT WITH (NOLOCK) ON P.productTypeKey = PT.productTypeKey 

CROSS APPLY DelimitedSplit8K('10,260,34,102,15', ',')  ds 
inner join Location L2 on L2.locationKey = ds.Item

WHERE 1=1 
AND EXISTS ( SELECT 1 from ins_car_loc ICL WITH (NOLOCK) WHERE ICL.RptingInskey = SU.instructorKey and ICL.instructorKey = @instructorKey and ICL.endDt is NULL ) 


AND (@sessionStart IS NULL OR SU.sessionStart >= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart <= @sessionEnd) 
ORDER BY SU.sessionUnitKey 

Open in new window



here is the result:

sessionUnitKey	locationKey	locationKeyList	sessionStart	sessionEnd
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000

Open in new window


Here is the result before applying CROSS APPLY

sessionUnitKey	locationKey	locationKeyList	sessionStart	sessionEnd
171331	34	NULL	2015-03-04 07:15:00.000	2015-03-04 09:15:00.000
171978	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
171979	NULL	15,10,34,102,260	2015-03-01 08:00:00.000	2015-03-01 10:00:00.000
171980	NULL	15,10,34,102,260	2015-03-01 10:15:00.000	2015-03-01 12:15:00.000
172279	NULL	15,10,34,102,260	2015-03-01 06:00:00.000	2015-03-01 08:00:00.000
172280	NULL	15,10,34,102,260	2015-03-01 12:30:00.000	2015-03-01 14:30:00.000
172284	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172285	NULL	15,10,34,102,260	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172286	NULL	15,10,34,102,260	2015-03-09 15:15:00.000	2015-03-09 17:15:00.000
172287	NULL	15,10,34,102,260	2015-03-09 13:15:00.000	2015-03-09 15:15:00.000
172288	NULL	15,10,34,102,260	2015-03-09 08:15:00.000	2015-03-09 10:15:00.000
172353	NULL	15,10,34,102,260	2015-03-12 06:00:00.000	2015-03-12 08:00:00.000
172354	NULL	15,10,34,102,260	2015-03-12 08:00:00.000	2015-03-12 10:00:00.000
172357	NULL	15,10,34,102,260	2015-03-12 10:00:00.000	2015-03-12 12:00:00.000
172358	NULL	15,10,34,102,260	2015-03-14 06:00:00.000	2015-03-14 08:00:00.000
172359	NULL	15,10,34,102,260	2015-03-14 08:00:00.000	2015-03-14 10:00:00.000
172360	NULL	15,10,34,102,260	2015-03-14 10:00:00.000	2015-03-14 12:00:00.000
172373	NULL	15,10,34,102,260	2015-03-15 06:00:00.000	2015-03-15 08:00:00.000
172374	NULL	15,10,34,102,260	2015-03-15 08:00:00.000	2015-03-15 10:00:00.000
172375	NULL	4,5	2015-03-15 14:15:00.000	2015-03-15 16:15:00.000
172376	NULL	4,5	2015-03-15 16:30:00.000	2015-03-15 18:30:00.000
172377	NULL	4,5	2015-03-15 18:30:00.000	2015-03-15 20:30:00.000
172378	NULL	15,10,34,102,260	2015-03-09 06:00:00.000	2015-03-09 08:00:00.000
172379	NULL	15,10,34,102,260	2015-03-09 08:00:00.000	2015-03-09 10:00:00.000
172380	NULL	4,5,97,104,110,111,112,211,232,226,256	2015-03-09 11:00:00.000	2015-03-09 13:00:00.000
172381	NULL	15,10,34,102,260	2015-03-09 14:00:00.000	2015-03-09 16:00:00.000
172382	NULL	15,10,34,102,260	2015-03-09 16:00:00.000	2015-03-09 18:00:00.000

Open in new window

0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40578532
Any suggestions
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40578940
For this demonstration I need a locations table, which I have generated as follows:
CREATE TABLE Location
	([LocationKey] int, [LocationName] varchar(6))
;
	
INSERT INTO Location
	([LocationKey], [LocationName])
VALUES
	(1, 'loc1'),
	(3, 'loc3'),
	(4, 'loc4'),
	(5, 'loc5'),
	(9, 'loc9'),
	(10, 'loc10'),
	(15, 'loc15'),
	(34, 'loc34'),
	(102, 'loc102'),
	(171, 'loc171'),
	(185, 'loc185'),
	(192, 'loc192'),
	(205, 'loc205'),
	(223, 'loc223'),
	(260, 'loc260')
;

Open in new window


It is deliberately simple, just 2 fields but [LocationName] can ONLY come from this table.

Then we use the sample data you provided for sessions.
CREATE TABLE session
	([sessionUnitKey] int, [sessionStart] datetime, [sessionEnd] datetime, [unit] varchar(4), [unitOrder] varchar(4), [locationKey] varchar(4), [LocationName] varchar(34), [status] varchar(7), [sessionKey] int, [userKey] int, [firstName] varchar(9), [lastName] varchar(10), [sessionType] varchar(2), [ClassRoom] varchar(4), [locationkeylist] varchar(17))
;
	
INSERT INTO session
	([sessionUnitKey], [sessionStart], [sessionEnd], [unit], [unitOrder], [locationKey], [LocationName], [status], [sessionKey], [userKey], [firstName], [lastName], [sessionType], [ClassRoom], [locationkeylist])
VALUES
	(171933, '2015-02-01 14:30:00', '2015-02-01 16:30:00', NULL, NULL, '4', 'Blair High School - AM Class', 'enabled', 155690, 13783, 'Richard', 'Burge', 'BW', NULL, '4,5'),
	(172332, '2015-02-02 06:00:00', '2015-02-02 07:40:00', NULL, NULL, NULL, NULL, 'enabled', 155808, 42, 'Christine', 'Porter', 'BW', NULL, '192,223'),
	(172333, '2015-02-02 07:45:00', '2015-02-02 09:25:00', NULL, NULL, NULL, NULL, 'enabled', 155809, 42, 'Christine', 'Porter', 'BW', NULL, '192,223'),
	(172334, '2015-02-02 09:30:00', '2015-02-02 11:10:00', NULL, NULL, NULL, NULL, 'enabled', 155810, 42, 'Christine', 'Porter', 'BW', NULL, '192,223'),
	(172335, '2015-02-02 11:15:00', '2015-02-02 13:15:00', NULL, NULL, NULL, NULL, 'enabled', 155811, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172336, '2015-02-02 13:15:00', '2015-02-02 15:15:00', NULL, NULL, NULL, NULL, 'enabled', 155812, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172337, '2015-02-02 15:30:00', '2015-02-02 17:30:00', NULL, NULL, NULL, NULL, 'enabled', 155813, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172338, '2015-02-02 17:30:00', '2015-02-02 19:30:00', NULL, NULL, NULL, NULL, 'enabled', 155814, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172339, '2015-02-02 19:45:00', '2015-02-02 21:45:00', NULL, NULL, NULL, NULL, 'enabled', 155815, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172340, '2015-02-03 06:00:00', '2015-02-03 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155816, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(171329, '2015-02-05 07:15:00', '2015-02-05 09:15:00', NULL, NULL, '9', 'Olney - Fair Hill Shopping Center', 'enabled', 155530, 2645, 'Michael', 'Ryan', 'BW', NULL, NULL),
	(171330, '2015-02-05 09:15:00', '2015-02-05 11:15:00', NULL, NULL, '34', 'Potomac Woods Shopping Center', 'enabled', 155531, 2645, 'Michael', 'Ryan', 'BW', NULL, NULL),
	(172266, '2015-02-10 06:00:00', '2015-02-10 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155778, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172363, '2015-02-10 06:00:00', '2015-02-10 07:40:00', NULL, NULL, NULL, NULL, 'enabled', 155841, 42, 'Christine', 'Porter', 'BW', NULL, '1,3,9,171,205,185'),
	(172267, '2015-02-10 08:00:00', '2015-02-10 10:00:00', NULL, NULL, NULL, NULL, 'enabled', 155779, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172268, '2015-02-10 14:00:00', '2015-02-10 16:00:00', NULL, NULL, NULL, NULL, 'enabled', 155780, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172269, '2015-02-10 16:00:00', '2015-02-10 18:00:00', NULL, NULL, NULL, NULL, 'enabled', 155781, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(171332, '2015-02-10 17:30:00', '2015-02-10 19:30:00', NULL, NULL, '23', 'Germantown Regal Hoyts Cinemas BTW', 'enabled', 155533, 10264, 'Jay', 'VanGilder', 'BW', NULL, NULL),
	(172348, '2015-02-11 06:00:00', '2015-02-11 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155824, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172349, '2015-02-12 06:00:00', '2015-02-12 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155825, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172350, '2015-02-12 08:00:00', '2015-02-12 10:00:00', NULL, NULL, NULL, NULL, 'enabled', 155826, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172274, '2015-02-15 06:00:00', '2015-02-15 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155786, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172275, '2015-02-15 08:00:00', '2015-02-15 10:00:00', NULL, NULL, NULL, NULL, 'enabled', 155787, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172278, '2015-02-16 06:00:00', '2015-02-16 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155790, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172276, '2015-02-17 06:00:00', '2015-02-17 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155788, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172277, '2015-02-17 13:15:00', '2015-02-17 15:15:00', NULL, NULL, NULL, NULL, 'enabled', 155789, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172271, '2015-02-18 06:00:00', '2015-02-18 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155783, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172352, '2015-02-18 06:00:00', '2015-02-18 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155828, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172272, '2015-02-18 08:00:00', '2015-02-18 10:00:00', NULL, NULL, NULL, NULL, 'enabled', 155784, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172273, '2015-02-18 12:30:00', '2015-02-18 14:30:00', NULL, NULL, NULL, NULL, 'enabled', 155785, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172347, '2015-02-19 06:00:00', '2015-02-19 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155823, 42, 'Christine', 'Porter', 'BW', NULL, '15,10,34,102,260'),
	(172261, '2015-02-20 06:00:00', '2015-02-20 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155773, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172263, '2015-02-20 08:00:00', '2015-02-20 10:00:00', NULL, NULL, NULL, NULL, 'enabled', 155775, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172262, '2015-02-20 10:00:00', '2015-02-20 12:00:00', NULL, NULL, NULL, NULL, 'enabled', 155774, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(172270, '2015-02-20 12:00:00', '2015-02-20 14:00:00', NULL, NULL, NULL, NULL, 'enabled', 155782, 13783, 'Richard', 'Burge', 'BW', NULL, '15,10,34,102,260'),
	(171976, '2015-02-28 06:00:00', '2015-02-28 08:00:00', NULL, NULL, NULL, NULL, 'enabled', 155728, 10, 'Neal', 'Branthover', 'BW', NULL, '15,10,34,102,260')
;

Open in new window


If one inspects the [locationkeylist] column you see these distinct STRINGS (!!!! STRINGS, NOT Integers !!!)
|   LOCATIONKEYLIST |
|-------------------|
|            (null) |
| 1,3,9,171,205,185 |
|  15,10,34,102,260 |
|           192,223 |
|               4,5 |

Open in new window


Then we also create the inline table valued function kindly provided by Scott Pletcher
[dbo].[DelimitedSplit8K]

Right, now we use all 3 of these together, like this:
SELECT
      S.sessionUnitKey
    , S.locationkeylist
    , CA.SplitLocationKey
    , L.*
FROM session AS S
      CROSS APPLY (
            SELECT
                  item AS SPLITLOCATIONKEY
            FROM dbo.DelimitedSplit8K(S.locationkeylist, ',')
      ) AS CA
      INNER JOIN location AS L ON CA.SplitLocationKey = L.LocationKey
;

Open in new window


The results of this query are:
| SESSIONUNITKEY |   LOCATIONKEYLIST | SPLITLOCATIONKEY | LOCATIONKEY | LOCATIONNAME |
|----------------|-------------------|------------------|-------------|--------------|
|         171933 |               4,5 |                4 |           4 |         loc4 |
|         171933 |               4,5 |                5 |           5 |         loc5 |
|         172332 |           192,223 |              192 |         192 |       loc192 |
|         172332 |           192,223 |              223 |         223 |       loc223 |
|         172333 |           192,223 |              192 |         192 |       loc192 |
|         172333 |           192,223 |              223 |         223 |       loc223 |
|         172334 |           192,223 |              192 |         192 |       loc192 |
|         172334 |           192,223 |              223 |         223 |       loc223 |
|         172335 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172335 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172335 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172335 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172335 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172336 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172336 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172336 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172336 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172336 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172337 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172337 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172337 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172337 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172337 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172338 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172338 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172338 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172338 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172338 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172339 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172339 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172339 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172339 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172339 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172340 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172340 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172340 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172340 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172340 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172266 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172266 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172266 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172266 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172266 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172363 | 1,3,9,171,205,185 |                1 |           1 |         loc1 |
|         172363 | 1,3,9,171,205,185 |                3 |           3 |         loc3 |
|         172363 | 1,3,9,171,205,185 |                9 |           9 |         loc9 |
|         172363 | 1,3,9,171,205,185 |              171 |         171 |       loc171 |
|         172363 | 1,3,9,171,205,185 |              205 |         205 |       loc205 |
|         172363 | 1,3,9,171,205,185 |              185 |         185 |       loc185 |
|         172267 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172267 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172267 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172267 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172267 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172268 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172268 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172268 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172268 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172268 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172269 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172269 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172269 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172269 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172269 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172348 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172348 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172348 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172348 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172348 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172349 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172349 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172349 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172349 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172349 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172350 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172350 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172350 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172350 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172350 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172274 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172274 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172274 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172274 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172274 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172275 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172275 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172275 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172275 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172275 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172278 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172278 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172278 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172278 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172278 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172276 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172276 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172276 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172276 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172276 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172277 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172277 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172277 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172277 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172277 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172271 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172271 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172271 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172271 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172271 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172352 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172352 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172352 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172352 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172352 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172272 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172272 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172272 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172272 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172272 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172273 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172273 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172273 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172273 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172273 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172347 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172347 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172347 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172347 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172347 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172261 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172261 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172261 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172261 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172261 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172263 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172263 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172263 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172263 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172263 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172262 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172262 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172262 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172262 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172262 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         172270 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         172270 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         172270 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         172270 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         172270 |  15,10,34,102,260 |              260 |         260 |       loc260 |
|         171976 |  15,10,34,102,260 |               15 |          15 |        loc15 |
|         171976 |  15,10,34,102,260 |               10 |          10 |        loc10 |
|         171976 |  15,10,34,102,260 |               34 |          34 |        loc34 |
|         171976 |  15,10,34,102,260 |              102 |         102 |       loc102 |
|         171976 |  15,10,34,102,260 |              260 |         260 |       loc260 |

Open in new window


So, we use the FUNCTION to split the comma separated strings into rows using a CROSS APPLY;
THEN we can JOIN other tables to the values that were previously embedded into the comma separated strings.

A working demonstration can be used at: http://sqlfiddle.com/#!6/d6b91/3
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40579010
but how would i say I want the ones in the list 1

List 1: 4,5

Or list2: 15,10,34,102
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40579023
Option 1
SELECT
      S.sessionUnitKey
    , S.locationkeylist
    , CA.SplitLocationKey
    , L.*
FROM session AS S
      CROSS APPLY (
            SELECT item AS SPLITLOCATIONKEY FROM dbo.DelimitedSplit8K(S.locationkeylist, ',')
      ) AS CA
      INNER JOIN location AS L ON CA.SplitLocationKey = L.LocationKey

WHERE L.LocationKey IN ( 15,10,34,102 )
;

Open in new window


Option 2
SELECT
      S.sessionUnitKey
    , S.locationkeylist
    , CA.SplitLocationKey
    , L.*
FROM session AS S
      CROSS APPLY (
            SELECT item AS SPLITLOCATIONKEY FROM dbo.DelimitedSplit8K(S.locationkeylist, ',')
      ) AS CA
      INNER JOIN location AS L ON CA.SplitLocationKey = L.LocationKey
                              AND L.LocationKey IN ( 15,10,34,102 )
;

Open in new window


You could TRY these options at: http://sqlfiddle.com/#!6/d6b91/3
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40579037
sorry, missed something.
Option 1
SELECT S.sessionUnitKey
    , S.locationkeylist
    , CA.SplitLocationKey
    , L.*
FROM session AS S
      CROSS APPLY (
            SELECT item AS SPLITLOCATIONKEY FROM dbo.DelimitedSplit8K(S.locationkeylist, ',')
      ) AS CA
      INNER JOIN location AS L ON CA.SplitLocationKey = L.LocationKey

WHERE (
       L.LocationKey IN ( 4,5 )
      OR
       L.LocationKey IN ( 15,10,34,102 )
      )
;

Open in new window


Option 2
SELECT S.sessionUnitKey
    , S.locationkeylist
    , CA.SplitLocationKey
    , L.*
FROM session AS S
      CROSS APPLY (
            SELECT item AS SPLITLOCATIONKEY FROM dbo.DelimitedSplit8K(S.locationkeylist, ',')
      ) AS CA
      INNER JOIN location AS L ON CA.SplitLocationKey = L.LocationKey
                              AND (
                                    L.LocationKey IN ( 4,5 )
                                  OR
                                    L.LocationKey IN ( 15,10,34,102 )
                                  )
;

Open in new window


But the real point of my demonstration was to show you the impact of converting from comma separated strings into rows.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40579042
can I use this statement
L.LocationKey IN ( 4,5,15,10,34,102  ) which will an equivalent for OR
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40579051
of course
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now