0

I have two tables, with different columns that I would like to compare. There is an issue in our system with serial numbers, and I want to make sure that all of the serial numbers (CMMTTEXT - in comma delimitted form) in Table B are being transferred to Table A (SERLTNUM - where each individual serial number has its own line)

Basically, what I would like to try and do is take the SOPNUMBER's from the last 3 months (which I would get from Table C), then get all rows from Table B and Table A with the last 3 months SOPNUMBER's and then somehow to make sure all serial numbers in CMMTTEXT in Table B are in Table A as SERLTNUM.

I know how to get all of the data, but I'm not sure what I can do in order to compare the two columns in SQL when they have different data formats. I am trying to think if there is someway I can just use substr() to search CMMTTXT but don't know how I could then display rows where there was no match found.

The LNITMSEQ table is an ID that corresponds to different line items in an order.

Table A

+-----------+----------+----------+---------------+
| SOPNUMBER | LNITMSEQ | SERLTNUM | ITEMNMBR      |
+-----------+----------+----------+---------------+
| I327478   | 16384    | ABC123   | someItem      |
+-----------+----------+----------+---------------+
| I327478   | 32768    | DEF123   | someOtherItem |
+-----------+----------+----------+---------------+

Table B

+-----------+----------+-----------------------------+
| SOPNUMBER | LNITMSEQ | CMMTTEXT                    |
+-----------+----------+-----------------------------+
| I327478   | 16384    | ABC123,ABC124,ABC125,ABC126 |
+-----------+----------+-----------------------------+
| I327478   | 32768    | DEF123,DEF124,DEF125,DEF126 |
+-----------+----------+-----------------------------+

Table C

+-----------+-----------+
| SOPNUMBER | DATE      |
+-----------+-----------+
| I327478   | 5/20/2017 |
+-----------+-----------+
| I327479   | 5/21/2017 |
+-----------+-----------+
Adjit
  • 10,134
  • 12
  • 53
  • 98
  • can you please paste your query so far? – jimmy8ball May 30 '17 at 14:24
  • You will need to separate the CMTTEXT in table B into rows split by comma, held in line with the SOPNUMBER - you can then test each of the CMTTEXT values individually. – jimmy8ball May 30 '17 at 14:26
  • you will need a variation of the following to break your comma separated string into rows: - SELECT SOPNUMBER, LNITMSEQ, PARSENAME(REPLACE(Split.a.value(',', 'VARCHAR(max)'),'-','.'),1) 'CMMTTEXT' FROM ( SELECT SOPNUMBER, LNITMSEQ, CAST ('' + REPLACE([CMMTTEXT], ',', '') + '' AS XML) AS Data FROM [table b] ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) - You can then use the resulting table to join on a 3 part key from table A on SOPNUMBER, LNITMSEQ, CMMTEXT – jimmy8ball May 30 '17 at 14:30

2 Answers2

0

I have commented above, but a clearer answer can be found here for what you need:

SQL split values to multiple rows

jimmy8ball
  • 746
  • 5
  • 15
0

You can use FIND_IN_SET function like as follows 

SELECT * FROM TableA INNER JOIN TAableB
ON FIND_IN_SET(TableA.SERLTNUM, TableB.CMMTTEXT) > 0

FIND_IN_SET function returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings. for more detail see the manual

huhushow
  • 537
  • 4
  • 13