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

tucktech
tucktech used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Try this


declare @year int = '2011'
select * from Patient 
left join (select *,year(apptDate) apptYear from ApptTime 
			where apptdate in (select max(ApptDate) ApptDate from ApptTime group by PatientID,year(apptDate)) 
			and ApptType in ('PVT','DOS')) LastDate
on Patient.PatientID = LastDate.PatientID 
where LastDate.apptYear = @year

Open in new window

Ephraim WangoyaSoftware Engineer
Commented:
try
select B.ApptDate, A.ApptType, B.PatientID, C.Firstname, C.LastName
from ApptTime A
inner join (select MAX(X.ApptDate) ApptDate, X.PatientID
            from ApptTime X
            inner join Patient Y on Y.PatientID = X.PatientID
            where YEAR(X.ApptDate) = 2011
            and X.ApptType in ('PVT', 'DOS', 'PHY')
            group by X.PatientID) B on B.ApptDate = A.ApptDate and B.PatientID = A.PatientID
inner join Patient C on C.PatientID = B.PatientID

Open in new window

Author

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Anyone want to respond?

Author

Commented:
No responses... can anyone assist?
SharathData Engineer
Commented:
try this.

SELECT t1.ApptDate, t1.ApptType, t1.PatientID, P.Firstname, P.LastName
  FROM (SELECT *,max_ApptDate = MAX(YEAR(ApptDate)) OVER ( PARTITION BY PatientID)
          FROM ApptTime
         WHERE ApptType IN ( 'PVT', 'DOS', 'PHY' )) t1
  JOIN Patient P ON t1.PatientID = P.PatientID  
 WHERE max_ApptDate = 2011
       AND YEAR(ApptDate) = max_ApptDate
Commented:
Sorry to not have addressed this issue yet.  I got caught up on other projects.

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial