How to exclude a range of ICD9 codes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to exclude a range of ICD9 codes

Author

Topic

daveb
Starting Member

8 Posts

Posted - 2009-03-02 : 13:38:05

I am very new to the world of SQL, so please bear with me.
I have a MS SQL database and one of the tables contains an element called ICD9 (varchar, 6). I need to create a query that will exclude any patient with an ICD9 code with a value of 140 through 239 (any 4th or 5th digit) and also exclude any patient with an ICD9 code = 344 (any 4th or 5th digit).

How do I construct this query?

Thanks for your help.
Dave

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-02 : 13:46:32

Please show us a data example to make your issue more clear. We'll need to see the raw data of about 10 rows and then the expected output of the query.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 13:47:59

Will this work?


Select * 
  FROM OneOfTheTablesInMySQL
 WHERE ICD9 NOT BETWEEN (140 and 239) AND ICD9 <> 344

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 13:52:39

or maybe a bootleg way


Select * 
  FROM OneOfTheTablesInMySQL
 WHERE ICD9 < 140 AND ICD9 > 239 AND ICD9 <> 344

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-03-02 : 14:03:51

I don't think either will work since it sounds like the codes are a substring of the data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

daveb
Starting Member

8 Posts

Posted - 2009-03-02 : 14:04:36

yosiasz,

My ICD9 values are character and it appears from your solution that you are expecting a number.

tkizer,

ICD9 codes from my master table:
001
0010
0011
00139
0017
0018
0019
002
0020
0021
1380
13800
13811
13820
139
1390
1391
1398
13984
140
1400
14000
1401
14019
1403
1404
1405
1406
1407
1408
1409
141
1410
14100
14101
1411
14110
14119
1412
1413
1414

I would expect my new table to have the following ICD9 rows (values):
001
0010
0011
00139
0017
0018
0019
002
0020
0021
1380
13800
13811
13820
139
1390
1391
1398
13984

Does that clarify my question.

Thanks again to both of you for assisting me.
Dave

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-03-02 : 14:20:44

ok,sorry about that, how about this


Select * 
  FROM ICD
 WHERE CAST(LEFT(ICD9,3) AS INT) NOT BETWEEN 140 AND  239 
   AND CAST(LEFT(ICD9,3) AS INT) <> 344

daveb
Starting Member

8 Posts

Posted - 2009-03-02 : 14:37:27

The data is alphanumeric. The ICD9 code can start with say a "V" or an "E", ex) V9132 or 14300 or 143.

I apologize for my lack of clearly stating the appearance of the data.
Dave

obiron
Starting Member

23 Posts

Posted - 2009-03-03 : 09:02:12

Assuming:

First character is Alpha, or 1st two characters are 00 or desired code is at the start of the string.


select Clean_ICD9 from
(
Select 
 CASE
   WHEN left(ICD9,1) like '[A-Z]' THEN right(ICD9,len(ICD9)-1)
   WHEN left(ICD9,2) like '00' THEN right (ICD9),len(ICD9)-2)
   ELSE ICD9 as 'Clean_ICD9'
 END
)
WHERE cast(left(Clean_ICD9,2),int) not between 140 and 239 and cast(left(Clean_ICD9,2),int <> 344

I have not tested the above.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-03 : 09:09:17

[code]SELECT *
FROM Table1
WHERE SUBSTRING(ICD9, 1, 3) NOT BETWEEN '140' AND '239'
AND ICD9 NOT LIKE '340%'[/code]

E 12°55'05.63"
N 56°04'39.26"

daveb
Starting Member

8 Posts

Posted - 2009-03-05 : 09:49:21

Peso,
Thank you...your solution works great!

obiron,
I can't get your solution to work...error message 'The right function requires 2 argument(s).' I know your code was untested, but can you figure out what I need to do to fix your code?

Thanks again everybody for your help.
Dave

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-05 : 10:40:12

Change obiron's code to this..The braces are incorrect..thats it.

select Clean_ICD9 from
(
Select
CASE
WHEN left(ICD9,1) like '[A-Z]' THEN right(ICD9,len(ICD9)-1)
WHEN left(ICD9,2) like '00' THEN right (ICD9,len(ICD9)-2)
ELSE ICD9 as 'Clean_ICD9'
END
)
WHERE cast(left(Clean_ICD9,2),int) not between 140 and 239 and cast(left(Clean_ICD9,2),int <> 344

daveb
Starting Member

8 Posts

Posted - 2009-03-05 : 12:07:30

vijayisonly,

Thanks for your reply, but I get the following error:

Incorrect syntax near the keyword 'as'.

How do I fix this error.

Thanks again everybody.
Dave

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources