synapse
play so hard
60929 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
52841 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 |
