How can I do a FullText search in a field for values that are IDs in another table?

Question

Post reply

How can I do a FullText search in a field for values that are IDs in another table?

craig.bobchin

SSCrazy

Points: 2298
More actions
July 14, 2017 at 12:30 pm

#338863

All,
I've got a table with a notes field that can contain anything. I've set up a full text index on the table/Field so I can filter out records that contain certain terms in order to extract certain other data from the field.
WHERE NOT CONTAINS (A.[NNOTE], 'START OR STOP OR AUTH OR AM OR PM OR NP OR NDC OR TIME')
However I realized that the data I'm trying to extract also exists in another table in the database.
Is there a way of bouncing the Contains or Freetext against a select list something like this:
WHERE Contains(A.[NNOTE], Select ID from table)
Or is that not something I can do with a full test index or is there another way of doing this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

some sample data would help ....for all relevant tables please....and expected results for the sample data

heres one way to provide https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

craig.bobchin SSCrazy Points: 2298 More actions · Answer 2

J Livingston SQL - Friday, July 14, 2017 1:20 PM
some sample data would help ....for all relevant tables please....and expected results for the sample data
heres one way to provide https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

Sorry.

Here's the query:

Select NCLAIM ,NNOTE ,REPLACE(B.DX,'.','') as DXCode ,[ICDType] ,HCC From [dbo].[Box19_RAW_MEDMC_FT] A INNER JOIN [MEDMC].[dbo].[MCPCXTR] C ON A.NClaim = C.CClaim left join (Select ICD9 as DX, CAST('9' as varchar(2)) as ICDType,HCC From dbo._ICD10_HCC_DX_TABLE

UNION ALL

Select [ICD 10] as DX ,CAST('10' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE

UNION ALL Select [ICD-9 Legacy] as DX ,CAST('9' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE

UNION ALL Select [ICD-10 Legacy] as DX , CAST('10' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE ) B ON A.[NNOTE] = B.DX or RTRIM(A.[NNOTE]) Like ('% '+B.DX) or LTRIM(A.[NNOTE]) Like (B.DX+' %') or A.[NNOTE] Like ('% '+B.DX+' %') Where NOT CONTAINS (A.[NNOTE], 'START OR STOP OR AUTH OR AM OR PM OR NP OR NDC OR TIME OR MIN OR HOUR OR CLAIM OR P.A.C OR BETHANY OR LEE OR PA OR ACC OR CPT OR QT OR 2015 OR 2016') and B.DX is not null And A.[NNOTE] not between '00:00' and '24:00' Group by NCLAIM, NNOTE,REPLACE(B.DX,'.',''),[ICDType],HCC

The First table:
CREATE TABLE [_ICD10_HCC_DX_TABLE( [ICD-9 Legacy] VARCHAR(12) NOT NULL PRIMARY KEY ,[ICD9] VARCHAR(6) NOT NULL ,[ICD-10 Legacy] VARCHAR(13) NOT NULL ,[ICD 10] VARCHAR(7) NOT NULL ,HCC VARCHAR(3) NOT NULL ); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('ICD-9 Legacy','ICD9','ICD-10 Legacy','ICD 10','HCC'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08449','M08.449','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08451','M08.451','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08452','M08.452','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08459','M08.459','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08461','M08.461','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08462','M08.462','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08469','M08.469','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','E119','E.119','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08472','M08.472','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08479','M08.479','40');

Second Table:

CREATE TABLE [Box19_RAW_MEDMC_FT]( NCLAIM INTEGER NOT NULL PRIMARY KEY ,NNOTE VARCHAR(MAX) NOT NULL ); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989237,'Doe Jon'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989503,'MCR0025L changed action from - 90 to HO'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989331,'ANESTHESIA START TIME 0910 STOP TIME 1010 START 0910 - STOP 1010'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989331,'T-4 B-6 =10 x $47.50'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'Donald Duck'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'Donald Duck'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'EXTRA DIAGS E119');
Expected Result:

NCLAIM	NNOTE	DXCode	ICDType	HCC
202029968	I509 Z6826 J449 Z8545	J449	10	111
202029968	I509 Z6826 J449 Z8545	I509	10	85
202030163	M41.9 M47.16 M47.13 J44.9	J449	10	111

So what I want to do look through the NNOTES field in the BOX19 Table and see if any of the codes are in the ICD10_HCC table. Which is what I have in my Not Contains() Where clause. Which is running very slowly. I'm trying to figure out a way of speeding this up.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

craig.bobchin - Friday, July 14, 2017 3:57 PM
J Livingston SQL - Friday, July 14, 2017 1:20 PM
some sample data would help ....for all relevant tables please....and expected results for the sample data
heres one way to provide https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
Sorry.
Here's the query:
Select NCLAIM ,NNOTE ,REPLACE(B.DX,'.','') as DXCode ,[ICDType] ,HCC From [dbo].[Box19_RAW_MEDMC_FT] A INNER JOIN [MEDMC].[dbo].[MCPCXTR] C ON A.NClaim = C.CClaim left join (Select ICD9 as DX, CAST('9' as varchar(2)) as ICDType,HCC From dbo._ICD10_HCC_DX_TABLE
UNION ALL
Select [ICD 10] as DX ,CAST('10' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE
UNION ALL Select [ICD-9 Legacy] as DX ,CAST('9' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE
UNION ALL Select [ICD-10 Legacy] as DX , CAST('10' as varchar(2)) as ICDType ,HCC From dbo._ICD10_HCC_DX_TABLE ) B ON A.[NNOTE] = B.DX or RTRIM(A.[NNOTE]) Like ('% '+B.DX) or LTRIM(A.[NNOTE]) Like (B.DX+' %') or A.[NNOTE] Like ('% '+B.DX+' %') Where NOT CONTAINS (A.[NNOTE], 'START OR STOP OR AUTH OR AM OR PM OR NP OR NDC OR TIME OR MIN OR HOUR OR CLAIM OR P.A.C OR BETHANY OR LEE OR PA OR ACC OR CPT OR QT OR 2015 OR 2016') and B.DX is not null And A.[NNOTE] not between '00:00' and '24:00' Group by NCLAIM, NNOTE,REPLACE(B.DX,'.',''),[ICDType],HCC
The First table:
CREATE TABLE [_ICD10_HCC_DX_TABLE( [ICD-9 Legacy] VARCHAR(12) NOT NULL PRIMARY KEY ,[ICD9] VARCHAR(6) NOT NULL ,[ICD-10 Legacy] VARCHAR(13) NOT NULL ,[ICD 10] VARCHAR(7) NOT NULL ,HCC VARCHAR(3) NOT NULL ); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('ICD-9 Legacy','ICD9','ICD-10 Legacy','ICD 10','HCC'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08449','M08.449','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08451','M08.451','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08452','M08.452','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08459','M08.459','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08461','M08.461','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08462','M08.462','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08469','M08.469','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','E119','E.119','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08472','M08.472','40'); INSERT INTO [_ICD10_HCC_DX_TABLE(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5) VALUES ('71432','714.32','M08479','M08.479','40');
Second Table:
CREATE TABLE [Box19_RAW_MEDMC_FT]( NCLAIM INTEGER NOT NULL PRIMARY KEY ,NNOTE VARCHAR(MAX) NOT NULL ); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989237,'Doe Jon'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989503,'MCR0025L changed action from - 90 to HO'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989331,'ANESTHESIA START TIME 0910 STOP TIME 1010 START 0910 - STOP 1010'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989331,'T-4 B-6 =10 x $47.50'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'Donald Duck'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'Donald Duck'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'See claims 201989237 and 201989236 ER'); INSERT INTO [Box19_RAW_MEDMC_FT](NCLAIM,NNOTE) VALUES (201989235,'EXTRA DIAGS E119');
Expected Result:
NCLAIM NNOTE DXCode ICDType HCC
202029968 I509 Z6826 J449 Z8545 J449 10 111
202029968 I509 Z6826 J449 Z8545 I509 10 85
202030163 M41.9 M47.16 M47.13 J44.9 J449 10 111
So what I want to do look through the NNOTES field in the BOX19 Table and see if any of the codes are in the ICD10_HCC table. Which is what I have in my Not Contains() Where clause. Which is running very slowly. I'm trying to figure out a way of speeding this up.

sorry ...but your setup scripts contain many errors....please rejig it, test it yourself and then post.
also I can't understand where your expected results come from based on the sample data?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day