Query Help 1

[shannanl]

I have a table (see below after text). I want to return only the ICD-9 codes that have matching CPT CODES. So if I check for CPT codes 74160 and 74000 then I only want the following results:

789.05 ABDOMINAL PAIN, PERIUMBILIC
789.07 ABDOMINAL PAIN, GENERALIZED

I can not figure out how to do this. If I use SELECT ICD-9 FROM TableName WHERE CPT CODE = 74160 and CPT CODE = 74000 then I get all items. If I replace the "and" with "or" then I get nothing. How can I write this query to return what I need. - Thanks in advance for the help.

ICD-9 DESCRIPTION CPT CODE
789.05 ABDOMINAL PAIN, PERIUMBILIC 74160
789.05 ABDOMINAL PAIN, PERIUMBILIC 74000
789.07 ABDOMINAL PAIN, GENERALIZED 74160
789.07 ABDOMINAL PAIN, GENERALZIED 74000

 

[mrdenny]

If this what you are looking for?

SELECT DISTINCT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] IN (74160, 74000)

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[ESquared]

And in case you care, your table is not in even third normal form...

 

[shannanl]

Well it seems I still have a problem. I only want to return the ICD-9 code if it has a matching CPT code. In this case I am checking for two CPT codes. If there is an entry in the db for only one CPT code, the IN clause seems to return that also. In the example below I would only want icd-9 789.05 returned because there is no row for icd-9 789.07 that matches cpt code 74000.

ICD-9 DESCRIPTION CPT CODE
789.05 ABDOMINAL PAIN, PERIUMBILIC 74160
789.05 ABDOMINAL PAIN, PERIUMBILIC 74000
789.07 ABDOMINAL PAIN, GENERALIZED 74160

Using the IN clause it seems to return this:

789.05 ABDOMINAL PAIN, PERIUMBILIC
789.07 ABDOMINAL PAIN, GENERALIZED

Yes I am aware that they table is not correct. I inherited this table long ago and it ties to some queries in a program so I am kind of stuck with it.

 

[SQLSister]

does this get you what you want?

select distinct ICD-9 , DESCRIPTION from 
(SELECT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] =74160
union all
 SELECT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] =74000) a

"NOTHING is more important in a database than integrity." ESquared

 

[shannanl]

Did it cut some of your query off? It looks like it did.

 

[SQLSister]

No. It gave you the whole thing.

"NOTHING is more important in a database than integrity." ESquared

 

[shannanl]

That did it. Thanks for the help.

Shannan

 

[ESquared]

SQLSister,

Doesn't that query devolve to:

SELECT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] = 74160
union --[s][red]all[/red][/s]
SELECT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] = 74000

which devolves to

SELECT [b]DISTINCT[/b] ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] IN (74160, 74000)

which is the original query that Shannan said isn't right?

Try this:

SELECT ICD-9 , DESCRIPTION
FROM TableName 
WHERE [CPT CODE] IN (74160, 74000)
GROUP BY ICD-9, DESCRIPTION
HAVING COUNT(DISTINCT [CPT CODE]) = 2 -- the number of CPT codes provided

 

[SQLSister]

Nice Eric. It does presume the table does only have one record for each ICD_9, description, CPT code combo which may or may not be the case. > 1 might be a better choice than = 2 in that case.

Here's another direction to try to get this:

SELECT t1.ICD-9 , t1.DESCRIPTION
FROM TableName t1
join tablename t2 on t1.DESCRIPTION = t2.DESCRIPTION and t1.icd_9 = t2.icd_9 
WHERE t1.[CPT CODE] =74160 and t2.[CPT CODE] =74000

if you still have multiple you could add the distinct to that, but I'd try without first.

"NOTHING is more important in a database than integrity." ESquared

 

[ESquared]

I was imagining that the requirement was to be able to specify any number of CPT codes and get the diagnosis that had ALL of them.

And you're right I was assuming that ICD9 + description + CPT was unique in the table. That can be remedied... see the final query here:

create table #diagprocs (
   icd9 varchar(10),
   descr varchar(50),
   cptcode int -- probably should be a char data type though
)

insert #diagprocs values ( '789.05', 'ABDOMINAL PAIN, PERIUMBILIC', 74160 )
insert #diagprocs values ( '789.05', 'ABDOMINAL PAIN, PERIUMBILIC', 74000 )
insert #diagprocs values ( '789.05', 'ABDOMINAL PAIN, PERIUMBILIC', 74000 )
insert #diagprocs values ( '789.07', 'ABDOMINAL PAIN, GENERALIZED', 74160 )
insert #diagprocs values ( '789.07', 'ABDOMINAL PAIN, GENERALIZED', 74160 )

select icd9 , descr
from #diagprocs
where cptcode in (74160, 74000)
group by icd9, descr
having count(distinct cptcode) = 2 -- the number of cpt codes provided

select d.*
from
   #diagprocs d
   inner join (
      select icd9 , descr
      from #diagprocs
      where cptcode in (74160, 74000)
      group by icd9, descr
      having count(distinct cptcode) = 2 -- the number of cpt codes provided
   ) x on d.icd9 = x.icd9 and d.descr = x.descr

drop table #diagprocs