Avatar of tucktech
tucktech
 asked on

select last patient appointment if the last appointment was within this year.

SQL Server Query

Hello Experts,  I need to write a sql query for SQL 2000 that has the following tables and conditions.

Tables            Fields
ApptTime                        ApptDate, ApptType, PatientID
Patient            Firstname, LastName, PatientID

Output:
ApptDate, ApptType, PatientID, Firstname, LastName

Selection Criteria:
ApptType = ‘PVT’, ‘DOS’ or ‘PHY’  or not Equal to these values
The records selected from ApptTime must be the last appointment scheduled and then must be for a specific year for the acct types listed.

For Example:
      ApptDate      ApptType      PatientID
      1/5/2011                        PVT            1111
      2/15/2011      PHY            1111
      2/22/2012      PVT            1111
      2/15/2011      DOS            2222
      8/9/2010                        PVT            2222
      3/15/2012      XXX            2222

If the selection was for 2011, no records for patient 1111 would appear because this patient had an appointment in 2012 with the specified appointment types.
The record 2/15/2011 DOS 2222 would be selected for patient 2222 because the appt in 2012 was not part of the appointment types specified.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
tucktech

8/22/2022 - Mon
SOLUTION
sachinpatil10d

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Ephraim Wangoya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
tucktech

ASKER
Hello ewangoya,   I got your query working however there are some errors that I don't understand.

I only want paitients who had their LAST appointment in 2011.  I am getting patients who had appointments in 2011 and 2012.  If they had an appointment in 2012 their last appiontment was not in 2011.  Can you help me figure this one out?

Thanks
tucktech

ASKER
Anyone want to respond?
tucktech

ASKER
No responses... can anyone assist?
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
tucktech

ASKER
Sorry for my neglect.  I am giving points to what appeared to be most effective.  I have not yet completed this teask but I have had this open too long.  Sorry.