SQL Query - SQL Server Forums

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)

SQL Query

Author

Topic

lsmyth
Starting Member

2 Posts

Posted - 2004-08-27 : 11:15:24

basically i have a table which contains patient details including a unique identifier, and another table which contains details of all of their visits to a particular clinic. i need to produce a spreadsheet which contains all the patients details along with the date that they last attended the clinic.

because of the way the system is, i can only do it in one query and can't use any parameters.

the problem is that i am finding it hard to only select the last record for each patient, it is returning all the records, i.e. for each patient i am getting a row for each date they attended, not just the last. to get the last visit, u use max(date), but i don't know how to get that for each patient.

basically it is

for each patient
select row where date = (max)date

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-27 : 11:22:20

select A.row
from myTable as A
Inner Join (select patientId, date = max(date) From myTable) as B
On A.patientId, = B.patientId
and A.date = B.date

that should be a start

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 11:22:59

this should do:


select t1.* 
from patients t1 
	inner join (select patientId, max(visitDate) as visitDate from patients group by PatientId) t2 
	on (t1.PatientId = t2.PatientId) and (t1.visitDate = t2.visitDate)

Go with the flow & have fun! Else fight the flow :)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 11:23:26

that was close :)

Go with the flow & have fun! Else fight the flow :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-08-27 : 11:27:10

quote:
Close only counts in horsehoes and grenades

Corey

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-27 : 11:35:40

true, true... :)))

Go with the flow & have fun! Else fight the flow :)

lsmyth
Starting Member

2 Posts

Posted - 2004-08-31 : 06:17:59

quote:

Originally posted by spirit1

this should do:


select t1.* 
from patients t1 
	inner join (select patientId, max(visitDate) as visitDate from patients group by PatientId) t2 
	on (t1.PatientId = t2.PatientId) and (t1.visitDate = t2.visitDate)

i think that this is along the right idea of what i want to do, but when i tried this, but i get an error message that says that the sql statement is not ended correctly at the inner join line. i am using more than one table which may affect the join, i.e.


select t1.* 
from patients t3, t1 
	inner join (select patientId, max(visitDate) as visitDate from patients group by PatientId) t2 
	on (t1.PatientId = t2.PatientId) and (t3.visitDate = t2.visitDate)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-08-31 : 06:35:18

well you can't do that like that.
you also need to join t1 an t3 in some way:


select t1.* 
from patients t3
 inner join t1 on (something)
	inner join (select patientId, max(visitDate) as visitDate from patients group by PatientId) t2 
	on (t1.PatientId = t2.PatientId) and (t3.visitDate = t2.visitDate)

you'll need to tell us more. what is t3 and what is t1? is that the same table, or two different tables? and how do you want them to be joined?

Go with the flow & have fun! Else fight the flow :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources