Solved

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

Posted on 2012-03-27
4
208 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you very much!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Select * from 6 29
Compare data between two databases 16 81
Divide by zero error encountered. 2 28
Problem with SqlConnection 5 108
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

763 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now