Do I need a cursor or dynamic SELECT?

[ThaDoctor]

Okay, I'll try to be as helpful as possible:
I want to see all of the diagnoses that our patients have been given in the year 2006, and the number of times that diagnosis has been used.

I have a medical database that stores information about each time a patient visits the clinic. One of these fields contains a diagnosis for the patient, but the field is rtf-formatted NTEXT.

Because of this, I created another table with nothing but the names of possible diagnoses phrases to use as a search criteria.
The data is in table ENCOUNTER_DATA, column OBJECT_DATA.
The diagnosis codes to use as a "search for" are in table HOLDING, column DESCRIPTION
The visit dates (or instances) are in table ENCOUNTERS, column VISIT_DATE. (see join below)


Example of table HOLDING: One column

DESCRIPTION
-----------
Asthma #438.0
Diebetes Type 2 #250.00
Back Pain (Low) #476.1

The table ENCOUNTERS is needed because it contains the actual encounters, or patient visit dates, with ENCOUNTID as the primary key.

ENCOUNTID VISITDATE REVIEWEDBY ISCOMPLETE (VARIOUSOTHERS)
--------------- ------------------- --------------------- ------------------ ---------------
(GUID) 1999-06-09 00:00:00 JRC 1 not important
(GUID) 2006-05-04 00:00:00 TIM 0 not important

ENCOUNTER_DATA is a table that contains the actual NTEXT data for the patient chart. It is broken down by field, Assessments would be 102, Plans would be 103, Medications would be 104, Objectives would be 101, etc.

DataID EncountID Object_Type Object_Data LastModified
------------------ --------------------- ------------- ----------------------- ----------------
(GUID, irrelevant) (GUID) 104 {\rtf1\ansi\ansicpg1... irrelevant
(GUID, irrelevant) (GUID) 102 {\rtf1\ansi\ansicpg1... irrelevant
(GUID, irrelevant) (GUID) 102 {\rtf1\ansi\ansicpg1... irrelevant

The HOLDING table contains the phrases I am looking for in Object_Data. There are approx 2970 different diagnoses phrases in this table. My output I would like to see like this:

Expected results
DESCRIPTION INSTANCES
----------------------- ---------
Diebetes Type 2 #250.00 256
Back Pain (Low) #476.1 412
Cough (NOS) 61

Once I have that, I can manipulate the report as I see fit, like percentiles, top 10, etc.
The 'dummied up' version of my query is:

USE SW_CHARTS
SELECT DESCRIPTION, COUNT(VISIT_DATE) AS INSTANCES
FROM ENCOUNTERS
JOIN ENCOUNTER_DATA ON ENCOUNTERS.ENCOUNTID = ENCOUNTER_DATA.ENCOUNTID
WHERE OBJECT_TYPE = '102' AND OBJECT_DATA LIKE ***WHATEVER IS IN HOLDING***
AND VISIT_DATE BETWEEN '1/1/2006' AND '12/31/2006'

-- A MORE REALISTIC QUERY I CREATED IS AS FOLLOWS --
(however it still does not give the results I am looking for)

use sw_charts
declare @rownum int
declare @dx nchar(40)
select top 1 @dx=DESCRIPTION from HOLDING
set @rownum = 0

WHILE @rownum < 2970
BEGIN
set @rownum = @rownum + 1
-- print cast(@rownum as char(1)) + ' ' + @dx
select top 1 @dx=description from holding
where description > @dx
SELECT @dx, count(visit_date)
from encounters join encounter_data on encounters.encountid = encounter_data.encountid
where visit_date between '1/1/2006' and '12/31/2006'
and object_type = '102'
and object_data like '%' + @dx + '%'
END

Thank you for your helpful consideration.
Doc

 

[PHV]

And what about this simple query ?
SELECT H.Description, Count(*) Instances
FROM Holding H
, encounters E INNER JOIN Encounter_data D ON E.encountid = D.encountid
WHERE visit_date between '1/1/2006' and '12/31/2006'
and object_type = '102'
and object_data like '%' + H.Description + '%'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886