Solved

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

Posted on 2012-03-27
4
220 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
[X]
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
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

622 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