Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Help on best to approach SQL coding of ICD-9s

Posted on 2012-03-27
4
217 Views
Last Modified: 2012-03-27
I'm sure this is familiar to anyone having various health codes that have a mixture of alphanumeric characters.

For the simplest ones (skipping the ones that begin with "V"), I *thought* I was ok when converting the '001 - 999.99' range that is currently stored as a VARCHAR into an INT with a series of CASE statements for each grouping of ranges (although I realize these are actually decimal values but I haven't figured out how to add precision and scale in an applicable way)

For ex.

CASE WHEN CAST(mcd.LINE_DIAG1 AS INT) BETWEEN 001 AND 139.99 --convert STRING TO INTEGER FOR EASIER SEARCHING
      THEN 'Infectious and Parasitic Diseases'

But some of the data is not converting and I'm getting errors such as:

"Conversion failed when converting the varchar value '250.00' to data type int"

Maybe someone has been in this situation and could offer suggestions?

Many thanks in advance!
0
Comment
Question by:britpopfan74
  • 2
  • 2
4 Comments
 
LVL 9

Expert Comment

by:OCDan
ID: 37773199
convert(decimal(18,2),@test) instead of the cast as INT, seems to work ok for me.

declare @test varchar(10)
set @test = '129.99'

select CASE WHEN convert(DECIMAL(18,2),@test) BETWEEN 001 AND 139.99
      THEN 'Infectious and Parasitic Diseases'
     ELSE 'failed'
     END

Returns 'Infectious and Parasitic Diseases'
If is set @test to 140.00 it returns 'failed'

ALSO as you said you are dealing with some that have letters in front this may help:

declare @test varchar(10)
set @test = 'ABC129.99'

SELECT
CASE WHEN
convert(DECIMAL(18,2),substring(@test, PATINDEX ('%[0-9]%',@test) ,len(@test)))
BETWEEN 001 and 139.99
THEN 'Infectious and Parasitic Diseases'
ELSE 'failed'
END
--returns 'Infectious and Parasitic Diseases'

What this does is looks for the first number within the string and then trims everything before that point.

Hope it helps.
0
 

Author Comment

by:britpopfan74
ID: 37773359
Thank you so much for help with the conversion...

If I want the @test variable to be dynamic -- the mcd.LINE_DIAG1 is pulling the literal diagnosis from the claim at runtime -- how may I make this occur?
0
 
LVL 9

Accepted Solution

by:
OCDan earned 500 total points
ID: 37773372
just replace every occurence of @test with mcd.LINE_DIAG1 and it will work perfectly fine.

e.g.
select CASE WHEN convert(DECIMAL(18,2),mcd.LINE_DIAG1) BETWEEN 001 AND 139.99
      THEN 'Infectious and Parasitic Diseases'
     ELSE 'failed'
     END

and

SELECT
CASE WHEN
convert(DECIMAL(18,2),substring(mcd.LINE_DIAG1, PATINDEX ('%[0-9]%',mcd.LINE_DIAG1) ,len(mcd.LINE_DIAG1)))
BETWEEN 001 and 139.99
THEN 'Infectious and Parasitic Diseases'
ELSE 'failed'
END
0
 

Author Closing Comment

by:britpopfan74
ID: 37773532
Thank you very much!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question