Link to home
Create AccountLog in
Avatar of soozh
soozhFlag for Sweden

asked on

Simple TSQL query?

Hello,

I am using MS SQL Server 2008.

I have a table that has rows of data that describe a patients visit to an Eye Clinic.  Its is called VisitData.

There are a number columns that are relevant to this question.  

PatientId varchar(15) - the identity of the patient.
Eye char(1) - 'L' or 'R' - (for Left or Right)
OriginalVisit char(1) - 'Y' or 'N'   (for yes and no!)
Diagnose int.  (a value between 1 and 10)

How can i write a query that will only return the rows where patient's eye had a particular diagnose?  i.e. all the visits for a given patient and eye that match the required diagnose.

The diagnose is only given on the patients first visit - where OriginalVisit = 'Y'.  It is not defined in the other rows belonging to the patient and eye.

So i guess i need some sort of query that looks at each row and only returns it if it finds that the patients original for the given eye visit matches the required diagnose.
Avatar of oheil
oheil
Flag of Germany image

Maybe this:

SELECT * WHERE PatientId in 
(
  SELECT PatientId
  FROM VisitData WHERE Diagnose = 5;
)

Open in new window


As an example I am searching for Diagnosis 5.

Regards,

Oli
I had a typo. Try this:

SELECT * FROM VisitData WHERE PatientId in 
(
  SELECT PatientId
  FROM VisitData WHERE Diagnose = 5;
)

Open in new window


Oli
Avatar of soozh

ASKER

OK... nearly there!

The problem is that we must search compare eyes and not patients.

An eye is unique when combined with PatientID...

somthing like:

SELECT * FROM VisitData WHERE PatientId+Eye in
(
  SELECT PatientId+Eye
  FROM VisitData WHERE Diagnose = 5;
)
SOLUTION
Avatar of oheil
oheil
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of soozh

ASKER

which do you think would give best performance?  Or should they be the same?
The join solution of Wim should be preferred.

There is propably not a large performance difference as long as you do not preprocess the string concatenation (as ID+eye is your natural key in this case). But from an SQL point of view and in respect to the SQL optimization done by the server, take the join solution.

Oli
Avatar of Scott Pletcher
You don't need a join at all really:


SELECT *
FROM dbo.VisitData
WHERE
    OriginalVisit = 'Y' AND
    Diagnose IN (5)
    --AND Eye = 'L' --or 'R'
I think you do need the join, as it was stated that you need all records for this patient, not only the first one which has OriginalVisit set to 'Y'. So in my opinion, it is imperative that you get a subset with patients first, and then get all their visits. If however I am wrong on this, I would agree that you don't need a join in that case.


Best regards,

Wim
I think you're right ... I misinterpreted this:
"
How can i write a query that will only return the rows where patient's eye had a particular diagnose?  i.e. all the visits for a given patient and eye that match the required diagnose.
"
Avatar of soozh

ASKER

Ok thanks for all the advice.

Now I have realized I have a new problem!

With the advice given above I can select the correct "visit data" by looking at the original visit and the diagnose.

However a little later on I narrow down the selected data to a specific period.  This may remove the original visit – so I no longer know the diagnose for the eye.

I have a couple of solutions.  Either I use a trigger to make sure that the diagnose is copied to all the records for the eye, or I load the selected data into a temporary table and update each record with the correct diagnose.

Any coments?
Don't copy the diagnosis.  Instead, retain/"carry" the original diagnosis from the query that gets it all the way thru the final query.

You've got a table design problem here.  The diagnosis should be stored in a separate table relating only to the patient and date, not to a visit per se.  Presumably you could even "inherit" a diagnsis from another doctor, which you would then need a proper place to store (although you are likely to at least confirm the diagnosis on their first visit, in which case you could consider it your diagnosis as well, I guess).
Avatar of soozh

ASKER

Well i agree the design is wrong - but thats how it is...

How do you propose i "carry/retain" the original diagnose all the way through? By changing the logic at the end to accept more records?

I have a problem in that I cannot modify the database, or the way the end user uses the data.

My interfaces must be the same... i am just trying to speed up a slow process.
Avatar of soozh

ASKER

i am going to award points here soon... but before i close this i refer you to my next question which looks at the new problem.  It seemed unfair to pile a new question on this one!
However a little later on I narrow down the selected data to a specific period.  This may remove the original visit – so I no longer know the diagnose for the eye.

I have a couple of solutions.  Either I use a trigger to make sure that the diagnose is copied to all the records for the eye, or I load the selected data into a temporary table and update each record with the correct diagnose.

I am not a friend of triggers as they may affect performance in a complex environment or produce deadlocks or data explosion if you do not carefully design the solution.

In this case I recommend your trigger idea. The purpose of the trigger is clear and the effect is very local (only the same table), so I do not expect problems in future. Nevertheless be carefull: only fill empty fields, never overwrite fields, which are already written! Never write an empty diagnosis. Check in the trigger even if you are sure that you don't have these cases. You may have them in future.

Oli