Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2012-03-27
4
Medium Priority
?
223 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 2000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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