Link to home
Start Free TrialLog in
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.
SOLUTION
Avatar of sachinpatil10d
sachinpatil10d
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tucktech
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
Anyone want to respond?
No responses... can anyone assist?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.