CODEWITHSUNDEEP
sqlsql-server
user1880192
user1880192

Reputation: 51

SQL CROSS Apply with comma delimited list

I am trying to only show records that meet the location criteria. The dilemma is the locations are store in the comma delimited list which makes it harder.

Here is the code

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

SET @instructorKey = 1
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

and here is the sample output

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

What I need is the records where locationKeyList is withing a given criteria and locationKey is also withing that list

Upvotes: 0

Views: 1093

Answers (1)

Evaldas Buinauskas
Evaldas Buinauskas

Reputation: 14077

Don't you have to cross apply like that? 

CROSS APPLY
(
SELECT match
FROM DelimitedSplit8K('10,260,34,102,15', ',')
) as t(country)

Upvotes: 1

Related Questions