Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

927 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