title: SQL CROSS Apply with comma delimited list
body: <p>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.</p>
  
  <p>Here is the code</p>
  
  <pre><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 &gt;= @sessionStart) AND (@sessionEnd IS NULL OR SU.sessionStart &lt;= @sessionEnd) 
  ORDER BY SU.sessionUnitKey 
  </code></pre>
  
  <p>and here is the sample output</p>
  
  <pre><code>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
  </code></pre>
  
  <p>What I need is the records where locationKeyList is withing a given criteria and locationKey is also withing that list</p>