need Qry assistance

Question

need Qry assistance

rothjm

SSC Enthusiast

Points: 173
More actions
August 22, 2006 at 2:56 pm

#114213

I have two tables, one with Patients and one with Visits.
I need to select the latest Visit (all columns) for each Patient listed in tblPatients. I want one record for each Patient.
SELECT *
FROM tblVisit
INNER JOIN tblPatient ON tblVisit.ACCT = tblPatient.ACCT
Order BY ACCT ASC, SERV_DATE DESC
I tried DISTINCT and TOP.
I think I need DISTINCT per ACCT but I don't know how to code that. As you can tell, I am a rookie...not sure what I am doing.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

What is the data type of SERV_DATE ? Is it a true datetime ? Is it possible for a patient to have 2 visits in the same day, and could there ever be 2 records in tblVisit with the same SERV_DATE for the same patient ?

This is why posting table DDL and index/constraint definitions will usually get you a faster/more complete/more correct answer.

Sergiy SSC Guru Points: 110207 More actions · Answer 2

Which visit "DISTINCT per ACCT" you want to see?

First? Last? First for today? Last before today?

If you include Visit into returned set and you want to see only one per ACCT you must choose one.

_____________
Code for TallyGenerator

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Sergiy,

I think you overlooked this:

"I need to select the latest Visit (all columns) for each Patient listed in tblPatients. I want one record for each Patient."

That seems to be pretty straightforward, we just have to wait till rothjm supplies data structures and a sample, to see what "latest" means in this particular case...

DavidP-340734 Right there with Babe Points: 737 More actions · Answer 4

If you have unique patient ID's and Visit ID's try something like this:

SELECT *

FROM tblPatient,

tblVisit,

(SELECT DISTINCT

PatientID,

MAX(VisitDate) AS LatestVisit

FROM tblVisit

GROUP BY PatientID) AS TEST

WHERE tblPatient.PatientID = TEST.PatientID

AND tblVisit.VisitDate = TEST.LatestVisit

AND tblPatient.PatientID = tblVisit.PatientID

ORDER BY ACCT ASC, SERV_DATE DESC

rothjm SSC Enthusiast Points: 173 More actions · Answer 5

Yes it is a DateTime field.

There could be multiple records in the tblVisit but I do just want the latest record.

rothjm SSC Enthusiast Points: 173 More actions · Answer 6

Yes there is a unique Patient MedRec# and then ACCT#'s for each visit. The ACCT#'s duplicate in tblVisit because there are multiple updates...like if they are moved to another bed etc.

I will try your example. Thanks!

rothjm SSC Enthusiast Points: 173 More actions · Answer 7

DavidP,

Your code pulls the data I want...except...I don't want the columns from tblPatient.

I only want the fields from tblVisit.

Any ideas how to do that?

Thanks

rothjm SSC Enthusiast Points: 173 More actions · Answer 8

I think I figured it out.

Instead of: SELECT *

I did: SELECT tblVisit.*

I got it to work! Thanks for all your advice!