Query a database using a list of search phrases

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Query a database using a list of search phrases

Author

Topic

ThaDoctor
Starting Member

3 Posts

Posted - 2007-01-09 : 09:50:18

Hello all, and here's to a good new year.

I have been charged with running a seemingly simple report based on our SQL2000 medical database.
I basically want to see all the diagnoses we have used in the year 2006, and count the frequency in which they were used.

The results should be a simple looking as:
BACK PAIN | 38
ANXIETY | 47
...etc.

I have compiled a list of the possible diagnoses in a table called holding (DESCRIPTION, DATELASTMODIFIED) - this will be the words I am searching for in the database.

The count comes from the table ENCOUNTERS (visit_date)
The data is matched against big text in ENCOUNTER_DATA (object_data)

In my simple mind, it should be like this:

SELECT DESCRIPTION, 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' -- points to the diagnosis area of the chart
AND OBJECT_DATA LIKE {the description field from HOLDING table}
ORDER BY {highest frequecy/count}

I guess I would consider myself in the less-than-intermediate user group. Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 09:58:26

Sincve you didn't bother to post complete DDL, nor sample data nor your expected output based on the provided sample data, all we can give is some educated guesses.
Here is one

SELECT		h.Description,
		COUNT(*)
FROM		HOLDING AS h
LEFT JOIN	ENCOUNTER_DATA AS ed ON ed.OBJECT_DATA = h.Description
LEFT JOIN	ENCOUNTERS AS e ON e.EncountID = ed.EncountID AND e.Visit_Date BETWEEN '20060101' AND '20061231'
ORDER BY	2 DESC,
		1

Peter Larsson
Helsingborg, Sweden

ThaDoctor
Starting Member

3 Posts

Posted - 2007-01-09 : 10:25:10

Perhaps then my level of experience should be dropped a notch or two, as in my own limited experience I don't know how to describe it better.

This is how I have been doing it manually...

USE SW_CHARTS
SELECT COUNT(VISIT_DATE) AS INSTANCES
FROM ENCOUNTERS
JOIN ENCOUNTER_DATA ON ENCOUNTERS.ENCOUNTID = ENCOUNTER_DATA.ENCOUNTID
WHERE OBJECT_TYPE = '102' AND OBJECT_DATA LIKE '%Emphysema #492.8%'
AND VISIT_DATE BETWEEN '1/1/2006' AND '12/31/2006'

++++ RESULT ++++
INSTANCES
61

There are 397 different diagnoses like "Emphysema #492.8" that I want to search for. The data is ntext inside OBJECT_DATA, and also contains rtf formatting that is difficult to read through. There could be anywhere from 1 to 9 different diagnoses in a particular cell, which is why I either have to search the database manually, or come up with a way to search for a set of diagnoses already setup in another table (HOLDING).

If I have offended anyone with my ignorance, I apologize. I only practice medicine.

ThaDoctor
Starting Member

3 Posts

Posted - 2007-01-11 : 08:53:05

Anyone else have any ideas?

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:

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.
I honestly hope this is enough information to go on. Once again, 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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources