TWW - Complicated SQL Question

go to bottom

Message Boards » » Complicated SQL Question - Report Creation

Page [1]

synapse
play so hard
60939 Posts
user info
edit post

Lets say you have a doctors office which has a number of different doctors and many clients. Sometimes a client can be seen by a different doctor each time they come in.

Their database consists of the following tables:

2 Entity Tables

Clients -  ClientID...ClientName
Doctors -  DoctorID...DoctorName

3 Event Tables

OfficeVisits  - EventID...ClientID...VisitDate.......DoctorID
Physicals     - EventID...ClientID...PhysicalDate....DoctorID
Surgeries     - EventID...ClientID...SurgeryDate.....DoctorID

Each leftmost ID field in each table is the auto-generate type: the first patient in that table has PatientID of 1 and the most recently added client has the highest PatientID. Events work the same.

I want to create a caseload report for each doctor (one table for each doctor to work off of) which has a row for each patient they've had any contact with.

The columns of the report should look like this:

PatientName...VisitDate...PhysicalDate...SurgeryDate

The dates included should be the most recent interaction each doctor has with each client.

Thew way I've approached this is something like the following:
SELECT c.ID, MAX(VisitDate), MAX(PhysicalDate), MAX(SurgeryDate), v.DoctorID
FROM Clients c JOIN OfficeVisits v ON c.ClientID = v.ClientID LEFT OUTER JOIN Physicals p on C.ClientID = p.CLientID LEFT OUTER JOIN Surgeries s on c.ClientID = s.ClientID
GROUP BY c.ClientID, v.DoctorID

That works to some degree, but if Dr.Bob does a surgery on a client, and Dr.Mike has a office visit with the same client, the surgery date shows up on both doctors tables, as does the office visit. I want each doctors report to only show their own interactions with each client.

Any ideas on how to create a report like I need? I'm running MSSQL and can create a view to query off of if that would be helpful.

[Edited on February 9, 2007 at 12:10 PM. Reason : ]

2/9/2007 12:06:29 PM

scrager
All American
9481 Posts
user info
edit post

you are going to have to create client-doctor combinations then join the visits on from there. your visit joins are going to have to join on both clientid and doctor id

your current select is selecting the latest visit for each patient, then showing only the officevisit doctor.

you've got


(patient)->
  (latest office)->(doctor)
  (latest physical)
  (latest surgery)

you want


(patient,doctor)->
   (latest office)
   (latest physical)
   (latest surgery)

if every patient has an office visit before they have a physical or surgery with each doctor (meaning they have to have an office visit with A before Physical with A and they have to have an office visit with B before they can have surgery with B)
then

client join office visit on clientid join physical on clientid and v.doctorid join surgery on clientid and v.doctorid

would work
but if client can have an office visit with A then physical with A and Surgery with B without an office visit with B then that won't work.

[Edited on February 9, 2007 at 1:09 PM. Reason : .]

2/9/2007 1:06:44 PM

aaronburro
Sup, B
53068 Posts
user info
edit post

Assuming that OfficeVisits, Physicals, and Surgeries are all mutually exclusive (as in, a physical does not count as an OfficeVisit), I'd roll like this:

SELECT
c.clientID, MAX(v.date), MAX(p.date), MAX(s.date), d.docID
FROM
Clients c, OfficeVisits v, Physicals p, Surgeries s, Doctors d
WHERE
v.docID = d.docID AND p.docID = d.docID AND s.docID = d.docID AND
v.clientID = c.clientID AND p.clientID = c.clientID AND s.clientID = c.clientID
GROUP BY
d.docID, c.clientID;

I don't see that the left outer joins help any in this situation, so just do it straight (I'm also more familiar with the results of WHERE statements, so that's my bias). Also, you have to make sure that the docID is compared for each doctor, otherwise you will get the most recent date for any visit/physical/surgery, regardless of the doctor.

2/10/2007 12:31:19 AM

Message Boards » Tech Talk » Complicated SQL Question - Report Creation

Page [1]

go to top

Admin Options : move topic | lock topic