How to get PartId from table #trades where it have parts that have only map from

• ahmed_elbarbary.2010

  Hall of Fame

  Points: 3118

  More actions

  February 15, 2021 at 1:27 pm

  #3845198

  I work on SQL server 2012 I have issue I can't get Part Id  that have only map code type from and code value from

  and not have map code type to and code value to depend on table #map ?

  every part id must be found on table trades two times

  first row for same part for map from(code type from and code value from )

  second row for same part for map to(code type to and code value to )

  meaning every part must exist two time

  but if it exist as one time for part as map from and not have map to

  then this

  what I need to display because it not have map to

  as example

  parts 1410,1445 have map from code tyepfrom and codevalue from  only so it must display

  part 1348 no need to display or show because it have map from and map to

  so How to write query on sql server 2012 display parts from table trades that have map from code type and code value

  only and not have map to(code type to and code value to ) depend on table #map ?

  create table #trades
  (
  PartId int,
  CodeTypeId int,
  Code int,
  PartLevel int
  )
  insert into #trades(PartId,CodeTypeId,Code,PartLevel)
  values
  (1348,9090,13456,0),
  (1348,7070,13000,1),
  (1387,9090,13456,0),
  (1387,7070,13000,1),
  (1390,9090,13456,0),
  (1390,7070,13000,1),
  (1800,9095,13570,0),
  (1800,7075,14000,1),
  (1850,9095,13570,0),
  (1850,7075,14000,1),
  (1400,9090,13456,0),
  (1410,9090,13456,0),
  (1445,9095,13570,0),
  (1485,9095,13570,0)
  
  
   create table #map
   (
   MapId int,
   CodeTypeFrom int,
   CodeTypeTo int,
   CodeValueFrom int,
   CodeValueTo int
   )
   insert into #map(MapId,CodeTypeFrom,CodeTypeTo,CodeValueFrom,CodeValueTo)
   values
   (3030,9090,7070,13456,13000),
   (3035,9095,7075,13570,14000)

  Expected Result
  
   PartId    CodeTypeId    Code    PartLevel
   1400    9090    13456    0
   1410    9090    13456    0
   1445    9095    13570    0
   1485    9095    13570    0

• DesNorton

  SSC-Insane

  Points: 24386

  More actions

  February 15, 2021 at 2:59 pm

  #3845237

  Your first hurdle to overcome is that the From/to values in the mappings are not in ascending order.  Then your Type/Code values in the #trades table are also not in ascending order.

  The following code solves the issue for the provided sample data.

  -- First ensure that your [FROM] values are always less than your [TO] values
  WITH cteMap AS (
    SELECT ctv.CodeTypeFrom, ctv.CodeTypeTo, cvv.CodeValueFrom, cvv.CodeValueTo
    FROM #map AS m
    CROSS APPLY (SELECT CodeTypeFrom = min(ct.val), CodeTypeTo = MAX(ct.val)
                 FROM (VALUES (m.CodeTypeFrom), (m.CodeTypeTo)) AS ct(val)
                 ) AS ctv
    CROSS APPLY (SELECT CodeValueFrom = min(cv.val), CodeValueTo = MAX(cv.val)
                 FROM (VALUES (m.CodeValueFrom), (m.CodeValueTo)) AS cv(val)
                 ) AS cvv
  )
  -- Then count the number of records per PartId that have a mapping
  , cteCount AS (
    SELECT    t.PartId, Rcrds = COUNT( * )
    FROM      cteMap  AS m
    LEFT JOIN #trades AS t
           ON t.CodeTypeId BETWEEN m.CodeTypeFrom AND m.CodeTypeTo
          AND t.Code       BETWEEN m.CodeValueFrom AND m.CodeValueTo
    GROUP BY  t.PartId
  )
  -- Finally get all the records from the #trades table that do not have 2 records from the mapping
  SELECT    t.*
  FROM      #trades  AS t
  LEFT JOIN cteCount AS c
         ON t.PartId = c.PartId
  WHERE ISNULL(c.Rcrds, 0) != 2;
  

  ---

  How to post data/code on a forum to get the best help.