Last appointment.? - 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)

Last appointment.?

Author

Topic

JDavis
Starting Member

8 Posts

Posted - 2005-08-03 : 09:00:57

Hi there,
I'm having trouble with a query:


Select pp.patientid, pp.first, pp.middle, pp.last, pp.suffix, app.apptstart,df.listname
From (appointments as app inner join doctorfacility as df on app.facilityid = df.doctorfacilityid) inner join patientprofile as pp on app.ownerid = pp.patientprofileid
where app.apptstart <='7/27/2005' and pp.patientid like 'FS%' --and df.doctorfacilityid <> 33 and df.doctorfacilityid <> 53 and df.doctorfacilityid <> 6741
order by pp.patientid, app.apptstart

This returns ALL apointments for the patients that meet that criteria. However, I am just wanting to return the last appointment for each patient and not every appointment. I have looked a little at the Max() function, but not sure if that is what I need, or if there is a simple way to do this.
Any Ideas?

Thanks in advance,

JDavis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 09:14:17

Post the table structures, some sample data and expected result

Madhivanan

Failing to plan is Planning to fail

JDavis
Starting Member

8 Posts

Posted - 2005-08-03 : 10:06:47

Here is the table structures


CREATE TABLE [dbo].[Appointments] (
	[AppointmentsId] [int] IDENTITY (1, 1) NOT NULL ,
	[OwnerId] [int] NULL ,
	[FacilityId] [int] NULL ,
	[ApptStart] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[DoctorFacility] (
	[DoctorFacilityId] [int] IDENTITY (1, 1) NOT NULL ,
	[ListName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[PatientProfile] (
	[PatientProfileId] [int] IDENTITY (1, 1) NOT NULL ,
	[PatientId] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[First] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Middle] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Last] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[Suffix] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Here is some sample data: (There would be multiple patients, but here is a generic one)

PatientProfile Table:
PatientProfileID: 128983
PatientID: FS00001
First: John
Middle: Q
Last: DOE
Suffix: NULL

Appointments (would have several of these with different appointmentsid and a different date)

AppointmentsID: 185005
OwnerId: 128983
FacilityID: 1441
AppStart: 2002-08-05 09:00:00.000

DoctorFacility
DoctorFacilityID: 1441
ListName: ETHC Ft. Sanders Office

Here is what I'm currently getting:

FS00001 John Q Doe NULL 2004-07-27 14:30:00.000 ETHC Ft. Sanders Office
FS00001 John Q Doe NULL 2004-09-09 13:15:00.000 ETHC Ft. Sanders Office
FS00001 John Q Doe NULL 2004-11-18 13:00:00.000 ETHC Ft. Sanders Office
FS00011 Jack C Jones NULL 2002-08-02 15:45:00.000 ETHC Baptist Office
FS00011 Jack C Jones NULL 2002-08-09 10:15:00.000 ETHC Ft. Sanders Office
FS00011 Jack C Jones NULL 2002-11-13 13:00:00.000 ETHC Ft. Sanders Office
FS00012 Kim L Johnson NULL 2002-08-23 10:00:00.000 ETHC Ft. Sanders Office
FS00014 David NULL Smith Jr 2002-08-05 07:00:00.000 ETHC Ft. Sanders Office
FS00015 Bill F Smith NULL 2002-08-30 09:30:00.000 ETHC Ft. Sanders Office

Here is what I need to be getting:

FS00001 John Q Doe NULL 2004-11-18 13:00:00.000 ETHC Ft. Sanders Office
FS00011 Jack C Jones NULL 2002-11-13 13:00:00.000 ETHC Ft. Sanders Office
FS00012 Kim L Johnson NULL 2002-08-23 10:00:00.000 ETHC Ft. Sanders Office
FS00014 David NULL Smith Jr 2002-08-05 07:00:00.000 ETHC Ft. Sanders Office
FS00015 Bill F Smith NULL 2002-08-30 09:30:00.000 ETHC Ft. Sanders Office

I just want to get only the patients last appointments

I hope I included everything. Let me know if I didn't

Thanks,

JDavis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-03 : 10:33:26

One way of doing this is move the result to a table and do query based on that

1 Create table having the same structure of the result set
2 Insert into newtable(colums) your_join_query
3 Select * from newtable where AppStart in (select max(AppStart) from newtable group by
patientid)
4 Drop newtable

Madhivanan

Failing to plan is Planning to fail

JDavis
Starting Member

8 Posts

Posted - 2005-08-03 : 11:05:29

Great! that did what I needed!

Thanks for your help!

JDavis

JDavis
Starting Member

8 Posts

Posted - 2005-08-03 : 17:25:52

Oops, I looke over the results and it has greatly cut them down, but I still am getting a few duplicates. It cut them down from 4,753 to 956. I ran a distinct query on the patientid and came up with 713 patients. So, I still have about 243 records for the same patients. It seems the query took care of most of them, but didn't get all of them for some reason?

any ideas?

Thanks,

JDavis

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-04 : 01:09:36

It should work correctly
can you post those data?

Madhivanan

Failing to plan is Planning to fail

JDavis
Starting Member

8 Posts

Posted - 2005-08-04 : 16:36:22

Here are the results:

FS00001 John R Brooks NULL 2002-08-05 09:00:00.000 ETHC Ft. Sanders Office
FS00007 Don NULL Colby NULL 2004-11-18 13:00:00.000 ETHC Ft. Sanders Office
FS00011 John D Wyrick NULL 2002-11-13 13:00:00.000 ETHC Ft. Sanders Office
FS00012 Jane M Ledbetter NULL 2002-08-23 10:00:00.000 ETHC Ft. Sanders Office
FS00014 Ron NULL Tilley NULL 2002-08-05 07:00:00.000 ETHC Ft. Sanders Office
FS00015 John L Morris NULL 2004-04-28 13:45:00.000 ETHC Ft. Sanders Office
FS00016 Don NULL Buchanan NULL 2004-09-09 10:30:00.000 ETHC Ft. Sanders Office
FS00018 John M Smith NULL 2005-01-19 13:20:00.000 ETHC Ft. Sanders Office
FS00019 Ron NULL Hill NULL 2003-02-19 14:00:00.000 ETHC Ft. Sanders Office
FS00021 Ron R Mitchell NULL 2003-02-26 15:00:00.000 ETHC Ft. Sanders Office
FS00022 Don NULL Heifner NULL 2002-08-29 07:00:00.000 ETHC Ft. Sanders Office
FS00024 John NULL Conwell NULL 2003-02-26 14:45:00.000 ETHC Ft. Sanders Office
FS00025 Jane NULL Roberts NULL 2005-07-15 14:30:00.000 ETHC Baptist Office
FS00026 Don L Weaver NULL 2003-09-11 13:45:00.000 ETHC Ft. Sanders Office
FS00026 Don L Weaver NULL 2003-12-30 13:45:00.000 ETHC Ft. Sanders Office
FS00026 Don L Weaver NULL 2004-04-06 13:45:00.000 ETHC Ft. Sanders Office
FS00026 Don L Weaver NULL 2004-07-29 13:00:00.000 ETHC 8th Floor Baptist
FS00026 Don L Weaver NULL 2005-04-01 07:30:00.000 ANC Papermill Pointe
FS00028 John R Tyson NULL 2002-11-12 15:00:00.000 ETHC Ft. Sanders Office
FS00029 Ron NULL Wells NULL 2002-12-19 14:45:00.000 ETHC Ft. Sanders Office
FS00030 John L Cooper NULL 2002-12-18 15:15:00.000 ETHC Ft. Sanders Office
FS00030 John L Cooper NULL 2005-02-24 11:15:00.000 ETHC 8th Floor Baptist
FS00030 John L Cooper NULL 2005-04-21 10:00:00.000 ETHC 8th Floor Baptist
FS00031 Don L Blevins NULL 2003-06-11 12:45:00.000 ETHC Ft. Sanders Office
FS00031 Don L Blevins NULL 2005-06-20 10:30:00.000 ANC Papermill Pointe
FS00033 Ron NULL Greenlee NULL 2004-07-27 13:30:00.000 ETHC Ft. Sanders Office
FS00033 Ron NULL Greenlee NULL 2004-10-26 13:00:00.000 ETHC Ft. Sanders Office
FS00033 Ron NULL Greenlee NULL 2004-11-18 13:45:00.000 ETHC Ft. Sanders Office
FS00033 Ron NULL Greenlee NULL 2004-12-16 13:00:00.000 ETHC Ft. Sanders Office
FS00034 Jane NULL Emert NULL 2003-07-10 14:45:00.000 ETHC Ft. Sanders Office
FS00035 Don H Bolden NULL 2003-06-11 13:00:00.000 ETHC Ft. Sanders Office
FS00039 Jane NULL Weaver NULL 2004-08-18 13:00:00.000 ETHC Ft. Sanders Office

I am getting them after running this query:


CREATE TABLE TmpFSResults (Patientid varchar(15), First varchar(30),Middle varchar(30), Last varchar(30),Suffix varchar(20),Apptstart datetime, ListName varchar(60))
Insert into TmpFSResults(PatientID,First,Middle,Last,Suffix,ApptStart, Listname)

Select pp.patientid, pp.first, pp.middle, pp.last, pp.suffix, app.apptstart,df.listname
From (appointments as app inner join doctorfacility as df on app.facilityid = df.doctorfacilityid) inner join patientprofile as pp on app.ownerid = pp.patientprofileid
where app.apptstart <='7/27/2005' and pp.patientid like 'FS%' --and df.doctorfacilityid <> 33 and df.doctorfacilityid <> 53 and df.doctorfacilityid <> 6741
order by pp.patientid, app.apptstart

select *
From TmpFSResults
where apptstart in
	(select Max(apptStart) from 
	TmpFSResults group by patientid)
order by patientid, apptstart

Drop Table TmpFSResults

It is limiting the results from 4753 to 956. So it does appear to be working on some of the records, but not on all of them?

Thanks,

JDavis

JDavis
Starting Member

8 Posts

Posted - 2005-08-04 : 18:13:57

Ahh nevermind, It is working now. I changed the query to this:


CREATE TABLE TmpFSResults (Patientid varchar(15), First varchar(30),Middle varchar(30), Last varchar(30),Suffix varchar(20),Apptstart datetime, ListName varchar(60)) --Create a tmp table to store data from the first query
CREATE TABLE TmpFSResults1 (Patientid varchar(15),Apptstart datetime) --Create another tmp table to limit and filter the data
CREATE TABLE TmpFSResults2 (Patientid varchar(15), First varchar(30),Middle varchar(30), Last varchar(30),Suffix varchar(20),Apptstart datetime, ListName varchar(60)) --Create a third tmp table to store filtered results

Insert into TmpFSResults(PatientID,First,Middle,Last,Suffix,ApptStart, Listname) --Insert data from first select query into first tmp table
Select pp.patientid, pp.first, pp.middle, pp.last, pp.suffix, app.apptstart,df.listname
From (appointments as app inner join doctorfacility as df on app.facilityid = df.doctorfacilityid) inner join patientprofile as pp on app.ownerid = pp.patientprofileid
where app.apptstart <=getdate() and pp.patientid like 'FS%' --and df.doctorfacilityid <> 33 and df.doctorfacilityid <> 53 and df.doctorfacilityid <> 6741
order by pp.patientid, app.apptstart

Insert into TmpFSResults1(PatientID,ApptStart) --Insert data into 2nd tmp table
select patientid, max (apptstart) from tmpfsresults group by patientid

Insert into TmpFSResults2(PatientID,First,Middle,Last,Suffix,ApptStart, Listname) --Insert data from filtered query into third tmp table
select tfs.patientid, tfs.first,tfs.middle,tfs.last,tfs.suffix,tfs.apptstart,tfs.listname
from tmpfsresults as tfs join tmpfsresults1 as tfs1 on tfs.patientid = tfs1.patientid
where tfs.apptstart = tfs1.apptstart --Query Data using comparison from both tables to filter results even more

select distinct * from tmpfsresults2 --get rid of duplicates
order by patientid,last,first

Drop Table TmpFSResults
Drop table tmpfsresults1
Drop table tmpfsresults2

Thanks so much madhivanan for all your help!

JDavis

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources