Finding patients without a diagnosis code

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

Transact-SQL (2000)

Finding patients without a diagnosis code

Author

Topic

Campbell
Starting Member

5 Posts

Posted - 2006-04-18 : 11:14:48

Hi SQLers

I have a problem with data extraction from my Hospital's Sybase database, which uses Transact-SQL.

I need to find those patients who have no diagnosis codes attached in their records (each patient should have at least one), and who started their treatment in the first week of January.

When diagnosis codes are attached to a patient, they are selected from one of two Diagnosis menus, entitled "Diagnosis" and "HRG". Sometimes the person attaching the code forgets to press "attach" after they have selected the code.

What I want to do is, for each patient on the database, count the number of Diagnosis codes that have been selected from the "Diagnosis" menu. If this number is 0, the patient is displayed in a results table, otherwise he/she is not.

The relevant tables on the database are "Patient" and "Diagnosis".

In the Patient table the relevant columns are PatientID, LastName, FirstName, StartDateTime and PatientSer (the patient's serial number).
In the Diagnosis table the relevant columns are DiagnosisCode, DiagnosisMenuName and PatientSer.

I have tried this:

SELECT
dbo.Patient.PatientID,
dbo.Patient.StartDate,
count(dbo.Diagnosis.DiagnosisCode)

FROM
dbo.Patient,
dbo.Diagnosis

WHERE
dbo.Patient.PatientSer = dbo.Diagnosis.PatientSer and
dbo.Diagnosis.DiagnosisMenuName="Diagnosis" and
( dbo.Patient.StartDateTime > "1/1/2006" and
dbo.Patient.StartDateTime <= "1/8/2006")

GROUP BY
dbo.Patient.PatientID

HAVING
count(dbo.Diagnosis.DiagnosisCode)>0

but this just produces the same row 2000 times over!

I've tried various variations on this, but there must be something fundamentally wrong with my syntax.

What must I do?!

TIA,

Campbell

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-18 : 11:28:05

You don't need to count if you're only interested in those with 0 rows, so I think you can simplify to this...

select * from Patient where '20060101' <= StartDateTime and StartDateTime < '20060108'
and PatientSer not in (select PatientSer from Diagnosis)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Campbell
Starting Member

5 Posts

Posted - 2006-04-18 : 12:24:42

Thanks very much

Your code worked after a fashion (thanks for the effort), but it only found those patients without any diagnosis code at all. What I was looking for is something that finds those patients who haven't had an entry from the "diagnosis" diagnosis menu, and who may or may not have had one from the "HRG" diagnosis menu. (Entries from either of these menus are classified as diagnosis codes, confusingly, so your code found ones with both missing.) That is why I put dbo.Diagnosis.DiagnosisMenuName="Diagnosis" in my code. I guess I should have made this clearer.

Cheers

Campbell

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-18 : 13:22:23

Okay. I misunderstood the problem.

If you post some example data and the expected result, it will be much easier to give you what you're asking. I think it's easy, but I want to be sure I understand - and an example is quite simply the best way...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Campbell
Starting Member

5 Posts

Posted - 2006-04-20 : 06:20:34

OK, here is my output table:

PatientID StartDateTime Count(DiagnosisCode)
--------- ------------- --------------------
ID1 3/1/2006 0
ID1 3/1/2006 0
ID1 3/1/2006 0
ID1 3/1/2006 0
ID1 3/1/2006 0
ID1 3/1/2006 0
ID1 3/1/2006 0
... (times 2000 rows all the same)

I think I may have confused you with many permutations of different things with 'diagnosis' in their names. To de-confuse the issue, here's a diagram of the relevant columns in the diagnosis table in our database:

Diagnosis
---------
DiagnosisMenuName (This can be either 'Diagnosis' or 'HRG')
DiagnosisCode (These are selected from one of the above two menus.)

The Count(DiagnosisCode) column in the results table above is intended to be a count of those diagnosis codes that were attached from the diagnosis menu entitled 'Diagnosis', not from the diagnosis menu entitled 'HRG'.

This particular patient did indeed have 0 diagnosis codes attached from the diagnosis menu entitled 'diagnosis'.
I suspect the fact that all the rows are the same is due to incorrect filtering of the Cartesian product.

What I wanted was this:

PatientID StartDateTime Count(DiagnosisCode)
--------- ------------- --------------------
ID1 3/1/2006 0
ID2 4/1/2006 0
ID3 5/1/2006 0

Your code almost worked (Again, thanks very much indeed for the effort), but what it did was find those patients who had no diagnosis code attached at all, from either of the two diagnosis menus; what I wanted was those patients who had no diagnosis code attached from just the diagnosis menu entitled 'diagnosis'.

I hope this makes things clearer.

Campbell

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 06:39:08

Okay, we have the expected result. Now for the example data...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Campbell
Starting Member

5 Posts

Posted - 2006-04-20 : 07:17:19

OK, sorry, I see what you mean.

(Please see also the edited reply above, explaining the Diagnosis Table structure in our database.)

Here is the example data (anonymised, naturally):

Patient_ID Attached_Diagnosis_Codes Start_Date
---------- ------------------------ ----------
ID1 HRG1 3/1/2006
ID2 HRG3 4/1/2006
ID3 Null 5/1/2006

(The Attached_Diagnosis_Codes are all taken from the diagnosis menu entitled 'HRG', or there are none at all. None are taken from the diagnosis menu entitled 'Diagnosis'.)

This should produce the following results:

PatientID StartDateTime Count(DiagnosisCode)
--------- ------------- --------------------
ID1 3/1/2006 0
ID2 4/1/2006 0
ID3 5/1/2006 0

Hope this sheds some light.

Campbell

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 08:18:10

Campbell,

You really need to work on how you explain a problem. It's easy:

1. Give the DDL (you did this - well done).
2. Give sample data for that DDL (I don't think I've seen this yet). Choose data which will demonstrate your various scenarios.
3. Give the results you would want from that sample data (you've done this, but without 2 it's not too useful).

I've guessed at 1 and 2 and produced it under 'data' below (this is what I would've loved to have had from you). If it's not right, then correct what I have produced and re-post it.

--data
set dateformat mdy
declare @Patient table (PatientID varchar(10), StartDateTime datetime, PatientSer varchar(10))
insert @Patient
          select 'ID1', '20060103', 'abc'
union all select 'ID2', '20060104', 'def'
union all select 'ID3', '20060105', 'xyz'

declare @Diagnosis table (PatientSer varchar(10), DiagnosisCode varchar(10), DiagnosisMenuName varchar(10))
insert @Diagnosis
          select 'abc', 'HRG1', 'HRG'
union all select 'abc', '1234', 'Diagnosis'
union all select 'def', 'HRG2', 'HRG'

--calculation (maybe?)
select * from @Patient where '20060101' <= StartDateTime and StartDateTime < '20060108'
and PatientSer not in (select PatientSer from @Diagnosis where DiagnosisMenuName = 'Diagnosis')

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Campbell
Starting Member

5 Posts

Posted - 2006-04-20 : 10:26:20

No need for me to post any more data, sir: your solution worked an absolute treat.

I am a newbie, you are a guru, I take my hat off to you.

Thanks again

Campbell

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-04-20 : 10:32:44

Your flattery certainly doesn't need working on!

Thanks for the feedback - and good luck...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources