Start Free Trial

asked on

sql query with a where clause

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

Give us a data mockup, both current data and expected return set, of what you're trying to pull off here.

you need to build the dynamic sql string that eventually looks like this

where session.locationkeyList in (1,2,3)

ASKER

Aneesh I just try that but it does not work

ASKER

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

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.

ASKER

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

ASKER

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

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.

>>> 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

sorry, I posted without seeing the full query.

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'

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

ASKER

ScottPletcher. Just ran the code and get no records back

ASKER

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

ASKER

the function produce 5 separate rows and then you saying equal to the list I think that may be an issue

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.

ASKER

The correct field is s.locationkeylist and it looks like the from my previous post is a comma delimited list of location ids

*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

OR because I don't know the data perhaps the correct field is L.locationkey

I do wish you would provide some sample data

ASKER

how would I handle the situation if I have 2 hubs which will be a different set of lists

ASKER

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#)

ASKER

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

ASKER

are there any update on this. Please advise.

>> 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.

ASKER

Can you show me a cross apply. I never used cross apply before.

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).

ASKER

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

ASKER

Any suggestions

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

ASKER

but how would i say I want the ones in the list 1

List 1: 4,5

Or list2: 15,10,34,102

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

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

can I use this statement
L.LocationKey IN ( 4,5,15,10,34,102 ) which will an equivalent for OR

of course